cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Prior Year value by corresponding week

Hi,

How can I get the value of same Week of prior year?

 

 

pic11.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Prior Year value by corresponding week

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! 

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Prior Year value by corresponding 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!

View solution in original post

7 REPLIES 7
Highlighted
Super User IX
Super User IX

Re: Prior Year value by corresponding week

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User VI
Super User VI

Re: Prior Year value by corresponding week

@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



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!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Solution Sage
Solution Sage

Re: Prior Year value by corresponding week

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! 

View solution in original post

Highlighted
Helper II
Helper II

Re: Prior Year value by corresponding week

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

Highlighted
Solution Sage
Solution Sage

Re: Prior Year value by corresponding 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!

View solution in original post

Highlighted
Helper II
Helper II

Re: Prior Year value by corresponding week

Thanks a lot Richard, it works too!
Highlighted
Solution Sage
Solution Sage

Re: Prior Year value by corresponding week

No Problem, 

 

glad to help

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors