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 hav...
  • 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

goncalogeraldes's avatar
goncalogeraldes
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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