Historical vs future data

[mmowrey]

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 on par with historcal .

[v-zhangti]

Hi, @mmowrey 

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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]

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