variance on Last week

[Anonymous]

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 

😀

[Anonymous]

@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]

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]

@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!

[goncalogeraldes]

My bad, sorry:

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

[Anonymous]

@goncalogeraldes 

sorry to bother you again! 

it looks for the previous week its not pulling through the 2022 previous week sales 

[goncalogeraldes]

Do you need it to be only 2022 or dynamic?

[Anonymous]

@goncalogeraldes  dymanic  would be better but as this is the 1st report I have done in Power BI I will take 2022  😄

[goncalogeraldes · Accepted solution]

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]

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]

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]

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