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 @summer18,
Assuming you have a Date table the following Measure should Work
Prior Year Amount Week = Calculate(sum('fact'[amount]),
filter(all('dates'), 'dates'[year] = max('date'[year])-1 && 'dates'[week] = max('dates'[week]))
I would also suggest removing the month attribute from your table as months and weeks do not align nicely unless of course you have a 445/544/454 style calendar.
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @summer18,
assuming you want the cummulative total by year, the measure would be
Cummulative Prior Year Amount Week = Calculate(sum('fact'[amount]),
filter(all('dates'), 'dates'[year] = max('date'[year])-1 && 'dates'[week] <= max('dates'[week]))
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @summer18,
Assuming you have a Date table the following Measure should Work
Prior Year Amount Week = Calculate(sum('fact'[amount]),
filter(all('dates'), 'dates'[year] = max('date'[year])-1 && 'dates'[week] = max('dates'[week]))
I would also suggest removing the month attribute from your table as months and weeks do not align nicely unless of course you have a 445/544/454 style calendar.
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi Richard @richbenmintz,
Thanks your formula works. But I have another problem with running total. How to get the running total work based from the formula you provided on Prior Year Amount Week?
I tried on my formula but it only copies my Prior Year value and does not display as running total by week
Hi @summer18,
assuming you want the cummulative total by year, the measure would be
Cummulative Prior Year Amount Week = Calculate(sum('fact'[amount]),
filter(all('dates'), 'dates'[year] = max('date'[year])-1 && 'dates'[week] <= max('dates'[week]))
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
No Problem,
glad to help
Proud to be a Super User!
@summer18 You need a date table that has the week number in it. Then you can just use a measure to find the curren week number, then find the same week number for the prior year.
But we'd need data to work with to assist. Cannot work with an image. Images are good for your end goal of what you want, but not with using it to provide sample measures.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@summer18 , I have discussed the same in both
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
In below on check comments
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |