topic Re: variance on Last week in DAX Commands and Tips

variance on Last week

Anonymous — Wed, 06 Jul 2022 10:51:03 GMT

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

😀

Re: variance on Last week

goncalogeraldes — Wed, 06 Jul 2022 11:07:29 GMT

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

Re: variance on Last week

Anonymous — Wed, 06 Jul 2022 13:52:30 GMT

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

Re: variance on Last week

goncalogeraldes — Wed, 06 Jul 2022 14:13:47 GMT

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

Re: variance on Last week

Anonymous — Thu, 07 Jul 2022 09:46:56 GMT

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 )

Re: variance on Last week

goncalogeraldes — Thu, 07 Jul 2022 10:17:06 GMT

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

Re: variance on Last week

Anonymous — Thu, 07 Jul 2022 10:58:17 GMT

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!

Re: variance on Last week

goncalogeraldes — Thu, 07 Jul 2022 14:13:26 GMT

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 )

Re: variance on Last week

Anonymous — Thu, 07 Jul 2022 13:13:57 GMT

goncalogeraldes

sorry to bother you again!

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

Re: variance on Last week

goncalogeraldes — Thu, 07 Jul 2022 14:14:30 GMT

Do you need it to be only 2022 or dynamic?

Re: variance on Last week

Anonymous — Thu, 07 Jul 2022 14:33:57 GMT

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

Re: variance on Last week

goncalogeraldes — Thu, 07 Jul 2022 14:55:03 GMT

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: