Creating Measurement between fixed Date and calculated Date

[Anonymous]

'm interested in creating a report comparing a before and after of two equivalent time periods.

The after period is looking at the days from a fixed date (1/10/20) to the most recent case in the tbl_Data table (in this case it is 92 days).

The before period would subtract the calculated 92 days from the fixed date (1/10/20).

I was able to get the days between part fairly easily using the following:

Days_Between = calculate( countrows(date_table), DATESBETWEEN( date_table[Date_field].[Date], date(2020,10,01), MAX(tbl_Data[Date Received]) ))

However I'm at odds on how to subtract this from the fixed date to get a date range I can filter on easily.

Any pointers/ideas would be greatly appreciated.

Cheers

[Anonymous]

Thanks! I would like to avoid entering the exact day as this will change when i refresh my data source. Using my measurement in lieu of the '92' in your formula seemed to work. Thanks very much 🙂

[wdx223_Daniel]

datesinperiod(date_table[Date_field].[Date],date(2020,10,1),-92,DAY)

[amitchandak · Accepted solution]

@Anonymous , You can create measure like this and try

Rolling -92 = CALCULATE(countrows(date_table),DATESINPERIOD('Date'[Date ],date(2020,10,01),-92,Day))

Rolling 92 = CALCULATE(countrows(date_table),DATESINPERIOD('Date'[Date ],date(2020,10,01),92,Day))