Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am working on a dashboard for some days now but I am unable to find a solution to my problem of including WoW changes for data on a weekly basis. I would love to include a third column to the right, that shows the WoW change.
The data I have is rather simple with the following two columns.
rec_week receipts
201803 561
201804 1594
201805 846
201806 953
...
...
Thanks so much in advance! I checked several threads for this, but non worked somehow. I am relatively new to Power BI, btw.
Philipp
Solved! Go to Solution.
HI @PhilST,
If you just want to calculate the WoW add the following measure to your model then put it on a table visual.
WoW = VAR weekselection = MAX ( Table1[rec_week] ) RETURN SUM ( Table1[receipts] ) - CALCULATE ( SUM ( Table1[receipts] ); FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = weekselection - 1 ) )
Be aware that this won't work for previous year, do you need to pick from last week of previous year also?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @PhilST,
If you just want to calculate the WoW add the following measure to your model then put it on a table visual.
WoW = VAR weekselection = MAX ( Table1[rec_week] ) RETURN SUM ( Table1[receipts] ) - CALCULATE ( SUM ( Table1[receipts] ); FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = weekselection - 1 ) )
Be aware that this won't work for previous year, do you need to pick from last week of previous year also?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
Thanks for the quick response!
I get the following error message when adding the measure you proposed:
"The syntax for ';' is incorrect. (DAX(VAR weekselection = MAX ( Table1[rec_week] )RETURN SUM ( Table1[receipts] ) - CALCULATE ( SUM ( Table1[receipts] ); FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = weekselection - 1 ) )))."
Is there something I should adapt in the measure that you proposed or prepare beforehand?
I would need the same for YoY data, yep...
Thanks in advance!
Philipp
Hi @PhilST
Replace the dot comma ";" by comma "," should work as to do with DAX sintax and OS regional settings in my case it's dot comma.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot! Now it works. I see the WoW change as absolute value now. When I try to calculate the % out of it though, it takes the same row´s data, hence the % shows wrong. Would you please also be able to help on that? I assume I need a new measure for it?
A colleague with SQL knowledge tried to help but it seems like the language is slightly different.
Thanks in advance!
Philipp
Hi @PhilST,
To get the YOY changes and even if you don't have leaps in weeks you should change your measure to the folllowing:
WoW = VAR weekselection = MAX ( Table1[rec_week] ) RETURN SUM ( Table1[receipts] ) - CALCULATE ( SUM ( Table1[receipts] ); FILTER ( ALL ( Table1[rec_week] ); Table1[rec_week] = CALCULATE ( MAX ( Table1[rec_Week] ); Table1[rec_Week] < weekselection ) ) )
See result below as you can see even if I don't have data between 201645 and week 201701 and 201801 the result will be calculated.
Regarding the percentage just do this following measure:
WoW % = [WoW]/ SUM(Table1[Receipts]) OR WoW % = SUM(Table1[Receipts]) / [WoW]
You should use one of the above formulas depending on what is the base line you want to use on the image below you have the first calculation.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello,
Would it be possible to do this on a subset of activity? i.e if Country was a field and you wanted to calculate the WoW% change, grouped by Country?
I've been trying to adapt your solution with no luck so far.
Thanks
M
Thank you very much MFelix!
Working perfectly. I really need to learn DAX 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |