Forum Discussion

mmowrey's avatar
mmowrey
Icon for Helper I rankHelper I
4 years ago

Historical vs future data

I have a list of events with future information. I am looking at 7 days rolling calendar. I want to add a column that looks at the historal average for those events so i can see if our projected is o...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

mmowrey , You can use relative date slicer.

 

You can create a column in date table

 

example

Date Type = SWITCH(TRUE(),'Date'[Date]<=TODAY() && 'Date'[Date]>=TODAY() -7,"Last 7 days"
,'Date'[Date]&"")

 

or for next 7 days

Date Type = SWITCH(TRUE(),'Date'[Date]>=TODAY() && 'Date'[Date]<=TODAY() +7,"Next 7 days"
,'Date'[Date]&"")

 

or rolling 7 formula

new measure =

CALCULATE(countrows(Table),DATESINPERIOD('Date'[Date],max('Date'[Date]),-7,DAY))

  • mmowrey's avatar
    mmowrey
    Icon for Helper I rankHelper I
    4 years ago

    does this work if i have a date slice looking 7 days ahead but i want my column to look back 365 days for average.. I don't want that historical column running off the 7 day ahead slicer..

     

    Thanks so much