cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Prior Year value by corresponding week

Hi,

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Highlighted
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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

7 REPLIES 7
Highlighted
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

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Proud to be a Super User!

Highlighted
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 provide sample data in the Power BI Forum

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

## 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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Highlighted
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

## 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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Highlighted
Helper II

## Re: Prior Year value by corresponding week

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

## Re: Prior Year value by corresponding week

No Problem,

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021