Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating Measurement between fixed Date and calculated Date

'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 , 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))

3 Replies

  • 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))

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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 🙂