Dynamic Calculation Based on Date

[adityavighne]

Hi,

I have a date slicer

I want to select a date range and calculate % change equivalent to the same previous period(days)

eg. if I select 15-31 march I want to compare data with a 1-14 march and then calculate % change

Dimensions - Date and Daily Total Sales

It must be dynamic if I select 45 days then previous 45 days data must be compared with it.

[OwenAuger · Accepted solution]

@adityavighne

Assuming your slicer is based on a Date table, and the measure you want to evaluate is [YourMeasure], you can write a measure that looks something like this:

Change from Prior Period % =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR NumDaysSelected =
    COUNTROWS ( 'Date' )
    // Alternatively MaxDate - MinDate + 1

VAR CurrentMeasure = [YourMeasure]
VAR PreviousMeasure =
    CALCULATE (
        [YourMeasure],
        DATESINPERIOD ( 'Date'[Date], MinDate - 1, - NumDaysSelected, DAY )
    )
RETURN
    DIVIDE ( CurrentMeasure, PreviousMeasure ) - 1

The key feature is using DATESINPERIOD to create a date filter of the same number of days ending the day before the selected period.

Regards,

Owen