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

 

 

 

3 Replies

  • 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

       

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.