Forum Discussion
Prior Year value by corresponding week
Hi,
How can I get the value of same Week of prior year?
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!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!
7 Replies
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!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 weekHi 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!
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-Last-Week/ba-p/1051123summer18 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
