Previous Period Measure in Retail Calendar Logic

[PhMeDie]

Hi,

I'd appreciate your help on a DAX measure.

We are on a 4-4-5 calendar. I have a date table in which I have a column with the calendar date. Furthermore I have an additional column which indicates the comparable date one year ago. Because of our calendar logic I cannot use the SAMEPERIOD function because the dates are not necessarily shifted always by 365 days.

Fact table is linked to the calendar date column.

Can anyone help me to rewrite the SAMEPERIOD function so it also works in our retail calendar logic?

Thanks for your help.

Best,

Phil

[lc_finance]

Hi Phil,


You can replace SAMEPERIODLASTYEAR with DATESBETWEEN.
This formula takes 2 inputs : the beginning date and the ending date.

You can calculate the beginning date by:
- Using MIN on your Date table
- Use LOOKUPVALUE to find the corresponding day one year ago

You can calculate the ending date by
- Using MAX on your Date table
- Use LOOKUPVALUE to find the corresponding date one year ago

Once you have your beginning date and ending date, you can pass them to the DATESBETWEEN formula and it should give you exactly what you want

Does this help you?

LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[PhMeDie]

Hello,

and thanks for your answer.

It would resolve my problem partially. What if a user does not select continous dates, but maybe the 3 sundays in December and would like to compare them with the comparable sundays of the previous year? Then that formula wouldn't give me the right results.

My intution is trying to tell me that it must be possible by somehow filtering the previous date column on all the dates which are in the current context. But I cannot figure out how to do it.

[lc_finance]

Hi @PhMeDie ,

what you want to do is possible. You mentioned that 'I have a date table in which I have a column with the calendar date. Furthermore, I have an additional column which indicates the comparable date one year ago.' 

When a user selects something in Power BI (for example via a slicer), he is filtering the full rows not just some cells. This means that when the user selects the 3 Sundays in December he is filtering 3 rows. And these rows have the column 'which indicates the comparable date one year ago' . We can use this column to find the amount for the previous year.

Could you share a Power BI file? (using One Drive or another similar tool)

Based on your current file, I can recommend how to best do that

LC

[PhMeDie · Accepted solution]

Hi,

in the end the following formula did the trick.

Previous Year Revenue =

CALCULATE (

[Net Sales VAT excluded EUR],

FILTER (all(Calendar_day),Calendar_day[Commercial Date_Next Year ] in values(Calendar_day[Calendar Date])))

This approach requires that in the calendart able a second column is maintained containing the comparable date of last year.

Thanks anyways for your help.

Best,

Philipp