Question regarding reference periods

Anything related to PowerPivot and DAX Formuale
Posts: 11
Joined: Sat Feb 01, 2020 8:51 pm

Question regarding reference periods

Postby Eriha591 » Mon Oct 12, 2020 10:46 pm


I have a question regarding reference periods:

I have made two main measures: Sales and Sales LY where Sales LY is based on Sales with SAMEPERIODLSASTYEAR. Then I've made a ton of measures based on these two. Rolling 7 days, 30 days, 90 days, Sales MTD, rolling 7 days LY, 30 days LY, 90 days LY, Sales MTD LY etc etc.

The problem with these measures are that Sales LY looks only on the dates whereas in my business it is more interesting to compare, for example, Sunday 11 october 2020 with Sunday 13 october 2019 (instead of Friday 11 october which is the comparison I get by default when using SAMEPERIODLASTYEAR).

How should i create Sales LY in order the achieve the above? I have to make create such a general measure so that it can be used to create all other measures above. For example I want my rolling 7 days to be Monday - Sunday 5-11 october and my rolling 7 days LY to automatically be Monday - Sunday 7-13 october.


Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Question regarding reference periods

Postby MattAllington » Tue Oct 13, 2020 4:01 pm

You are moving out of the world of inbuilt time intelligence and into the world of custom time intelligence. There are many ways to do this. One way I have seen is to add a day number to your calendar table to match the days between years.

This would normally be a manual process, but if you can write a rule in PQ, then you could automate it.
1 Jan 2019 was a Tuesday, so that might be 1
2 Jan is 2 (Wednesday)
3 Jan is 3 (Thursday)
And so on for 365 days

In 2020
Jan 1 was Wednesday, so make it 2, not 1

Each date in 2020 therefore can be matched back to the same day in any other year. You can do that with a filter, something like this

CALCULATE([sales],FILTER(ALL(Calendar),Calendar[year]=MAX(Calendar[Year])-1),VALUES(Calendar[Day of Year column])

The FILTER portion takes you back to the previous year but gives you every day. The values portion checks which days you need.

Read more here ... beginners/

One issue is “what do you do with the days that technically fall into the previous year, eg 31 Dec 2018
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests