Calculate variation based on a dynamic date filter

[Anonymous]

Hello, 

I´m trying to calculate absolute and percentual variations on my visualizations (scorecards and graphs) based on a dynamic filter date.

Let´s say if I choose the last 2 weeks/months on my filter, I want it to be compared to the previous 2 weeks/months.

Example:

Is that possible?

Thanks in advance!

[Mariusz]

Hi @Anonymous,

the below measure should be a good start, it will shift days in current selection ei. looking at weeks it will ofset 7 days.

If you need something that offsets months where the count of days differs its still possible. 

Time Shift =

VAR e = FIRSTDATE('Calendar'[Date]) -1

VAR s = e + DATEDIFF(LASTDATE('Calendar'[Date]) -1, e, DAY)

RETURN

CALCULATE(

Sales[sales],

FILTER(

ALL('Calendar'),

'Calendar'[Date] >= s && 'Calendar'[Date] <= e

)

)

Hope This Helps 
Mariusz

[Anonymous]

Hi Mariusz,

First, thanks a lot for your advice.

I´ve created the measure "Previous period" as it is below:

Then I tried the solution you suggested but I´m still running into a problem: after creating the measure I´ve set the date filter for 5 days but it doesn´t show the previous 5 days. Instead it´s just showing the last day for each of the day:

I guess I´m doing something wrong.

Could you help me out on that?

Thanks again!

[Mariusz]

Hi @Anonymous 

By Adding Date in your table you are applying a filter context so on one day it will offet only one day back, if you look at the totals it will give you your 5 days.

Let me know if I ansered you question, if not please reiterate your requierment.

Mariusz

[Anonymous]

Hi Mariusz,

Yes, I got it. You´re totally right, that worked just fine.

One more question: what if I choose March (31 days) and I want to compare it to February (28 days)? I´m affraid this solution won´t work.

Is there any way of having the two solutions in one? This way it would be possible to choose last week filters or last month filters.

Sorry for the bother.

[Anonymous]

Just in case you think it´s easier to demonstrate I´m sharing with you the link to PowerBI file:

https://www.dropbox.com/s/7los09d3hldp3sw/variance_with_dynamic_period.pbix?dl=0