Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
summer18
Helper III
Helper III

Prior Year value by corresponding week

Hi,

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

 

 

pic11.png

2 ACCEPTED SOLUTIONS
richbenmintz
Solution Sage
Solution Sage

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! 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

7 REPLIES 7
richbenmintz
Solution Sage
Solution Sage

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! 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Thanks a lot Richard, it works too!

No Problem, 

 

glad to help



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


edhans
Super User
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



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.