topic Historical vs future data in DAX Commands and Tips

Historical vs future data

mmowrey — Thu, 10 Feb 2022 15:21:46 GMT

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 .

Re: Historical vs future data

amitchandak — Thu, 10 Feb 2022 17:30:25 GMT

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

Re: Historical vs future data

mmowrey — Thu, 10 Feb 2022 18:05:11 GMT

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

Re: Historical vs future data

v-zhangti — Tue, 15 Feb 2022 07:08:16 GMT

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.