Dynamically calculating Last Period

[Anonymous]

Hey there,

I would like to create a measure for dynamically calculating the last period. I am using this calendar slicer:

The measure should be something like:

vs Last Period = CALCULATE(SUM(Forms[Ocurrences]), DATEADD('Calendar'[Date],-60,DAY)) 

 Yet, the time period should be dynamic not 60 days before.

To give an example: 

• When I change the period to Last 1 Year, the measure should do CALCULATE (DATEADD -365 days)
• When I change the period to Last 1 Month, the measure should do CALCULATE (DATEADD -30 days)
• When I change the period to Last 1 Week, the measure should do CALCULATE (DATEADD -7 days)

Looking forward to any suggestions! 

[Anonymous]

Hi @Anonymous,

You can create a new table to extract the raw table date value and use it as the source of the slicer. (it does not have a relationship to raw table fields)
Then you can use the following measure formula based on selection range to calculate the rolling total on each row:

Measure =
VAR currDate =
    MAX ( 'Table'[Date] )
VAR range =
    COUNTROWS ( ALLSELECTED ( 'Selector'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Date] >= currDate - range
                && [Date] <= currDate
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

I am not exactly sure what you mean by the first sentence. Could you elaborate a bit? Maybe even show a step-by-step guide? I can't really figure out what you used as Select (Date). Just to double check you used my original date table as Table(Amount), right?

[Anonymous · Accepted solution]

Hi @Anonymous,

'Table' means the fact table you used to calculate, the 'selector' means the date table you used. For my expression, it is calculated based on selected range day counts. (filter range equal to 'previous N[day count of selected date range] day of current date' to 'current date')

Hope the above helps you to clarify my expression and calculation logic.

Regards,

Xiaoxin Sheng

[parry2k]

@Anonymous I think we need to do some trick here to make it work as there is no way to find out what is selected in the relative date filter. Can you share a sample pbix file thru one drive/google drive and will try to work thru some solutions. Cheers!!