Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

variance on Last week

 

I am quite new to Power BI and currently working on the below.

 

I have the below table which has sales by week, I need to work out the variance between the weeks.

The issue I seem to have is that in the data I have a Year column which has two different year based on the date 

 

Any help would be greatly appreciated 

 

Thank you 

 

😀

 

 

  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    Hello there Anonymous ! I hope the following works for you! 😅

     

     

    vs LW% = 
VAR __previous_week =
    CALCULATE (
        SUM ( 'Appt Paste'[Sales] ),
        FILTER(
            'Appt Paste',
            YEAR ( 'Appt Paste'[Date] ) = YEAR ( TODAY() )
        ),
        DATEADD('Appt Paste'[Date], -7, DAY)
    )

VAR __current_sales =
    CALCULATE (
        SUM ( 'Appt Paste'[Sales] ),
        FILTER(
            'Appt Paste',
            YEAR ( 'Appt Paste'[Date] ) = YEAR ( TODAY() )
        )
    )
RETURN
    DIVIDE ( __current_sales - __previous_week, __previous_week, 0 )

     

     I've tested it witha  sample datset and worked for me:

     

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

11 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    Hello there Anonymous ! Hope this works for you:

    WoW variance % =
VAR __previous_week =
    CALCULATE ( SUM ( 'Table'[Sales] ), DATEADD ( 'Table'[Date], -7, DAY ) )

VAR __current_sales =
    SUM ( 'Table'[Sales] )

RETURN
    DIVIDE ( __current_sales - __previous_week, __previous_week, 0 )

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thank you Gonçalo Geraldes,  I used the formual but it doesnt seem to be pulling in any values 

      as I have two years in one table is this causing the issue? would it best practice be to have 2 different tables for each year?

       

       I have also tried to create two new columns in that table with the following formula: 

      This Week = IF([Week]=MAX([Week ]),"TW","")

      Last Week = IF([Week]=MAX([Week])-1,"LW","")

       

      Then create a new measure with the following formula:

      % on LW =

      CALCULATE(

      SUM('Appt Paste'[Sales]), 'Appt Paste'[This Week] IN {"TW"}

       

      )/CALCULATE(

      SUM('Appt Paste'[Sales]), 'Appt Paste'[Last Week] IN {"LW"})-1

       but again as two years are in the same table the this week column is returning week 52 as max rather than the max week 22

       

      Thanks 

      • goncalogeraldes's avatar
        goncalogeraldes
        Super User
        4 years ago

        Try this in a measure:

         

        WoW variance % =
VAR __previous_week =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1
            = WEEKNUM ( 'Table'[Date] )
    )
VAR __current_sales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ) ) = WEEKNUM ( 'Table'[Date] )
    )
RETURN
    DIVIDE ( __current_sales - __previous_week, __previous_week, 0 )

         

        For additional help, please @ me in your reply!

        You can also check out my LinkedIn!

        Best regards,
        Gonçalo Geraldes

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    goncalogeraldes 

     

    the below formula doesnt seem to change when i change the week 

     

     

     

     

    vs LW% = VAR __previous_week =
    CALCULATE (
    SUM ( 'Appt Paste'[Sales] ),
    WEEKNUM ( SELECTEDVALUE ( 'Appt Paste'[Week] ) ) - 1
    = WEEKNUM ( 'Appt Paste'[Week] )
    )
    VAR __current_sales =
    CALCULATE (
    SUM ( 'Appt Paste'[Sales] ),
    WEEKNUM ( SELECTEDVALUE ( 'Appt Paste'[Week] ) ) = WEEKNUM ( 'Appt Paste'[Week] )
    )
    RETURN
    DIVIDE ( __current_sales - __previous_week, __previous_week, 0 )
    • goncalogeraldes's avatar
      goncalogeraldes
      Super User
      4 years ago

      Hello there Anonymous ! Try this:

       

      vs LW% =
VAR __previous_week =
    CALCULATE (
        SUM ( 'Appt Paste'[Sales] ),
        WEEKNUM ( SELECTEDVALUE ( 'Appt Paste'[Week] ) ) - 1
            = WEEKNUM ( 'Appt Paste'[Week] ),
        YEAR ( 'Appt Paste'[Date] ) = MAX ( YEAR ( 'Appt Paste'[Date] ) )
    )
VAR __current_sales =
    CALCULATE (
        SUM ( 'Appt Paste'[Sales] ),
        WEEKNUM ( SELECTEDVALUE ( 'Appt Paste'[Week] ) )
            = WEEKNUM ( 'Appt Paste'[Week] ),
        YEAR ( 'Appt Paste'[Date] ) = MAX ( YEAR ( 'Appt Paste'[Date] ) )
    )
RETURN
    DIVIDE ( __current_sales - __previous_week, __previous_week, 0 )

       

      For additional help, please @ me in your reply!

      You can also check out my LinkedIn!

      Best regards,
      Gonçalo Geraldes

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        goncalogeraldes  

        i seem to get an error message which say the max function accepts a column reference as an argument. i am not sure what it means!