Forum Discussion

Stan_Lego's avatar
Stan_Lego
Frequent Visitor
5 years ago
Solved

I don't know why I cannot use the Rolling Average Quick Measures

I am trying to plot the 7 Day rolling average of CT, but i am unable to do so for some reason using the quick measures. Can anybody help me, will really appreciate it. Thanks!

 

PBI File: https://drive.google.com/file/d/1vRCu6cUpuenbHro2t3TD8E3oa6YRFL7z/view?usp=sharing 

General Comment
Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, Stan_Lego ;

    If you want to use rolling average base on [CheckInTime] , please try it.

    RoLL =
CALCULATE (
    AVERAGE ( [CT] ),
    FILTER (
        ALL ( TrackingTable ),
        DATEDIFF ( [CheckInTime], MAX ( [CheckInTime] ), DAY ) <= 6
            && DATEDIFF ( [CheckInTime], MAX ( [CheckInTime] ), DAY ) >= 0
    )
)

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Stan_Lego , with help from date table you should calculate rolling 7 days like

     

    example

    Rolling = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

     

     

    Yet to check the file

    • Stan_Lego's avatar
      Stan_Lego
      Frequent Visitor
      5 years ago

      amitchandak Yep I have tried but when it does not show anything when i want to plot it on a line graph

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Stan_Lego ;

    If you want to use rolling average base on [CheckInTime] , please try it.

    RoLL =
CALCULATE (
    AVERAGE ( [CT] ),
    FILTER (
        ALL ( TrackingTable ),
        DATEDIFF ( [CheckInTime], MAX ( [CheckInTime] ), DAY ) <= 6
            && DATEDIFF ( [CheckInTime], MAX ( [CheckInTime] ), DAY ) >= 0
    )
)

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Stan_Lego's avatar
      Stan_Lego
      Frequent Visitor
      5 years ago

      Hi v-yalanwu-msft 

       

      Thank you for your help, i just wanna ask why doesn't the graph start from zero and instead it starts from a particular value. Is it possible to make the graph only calculate rolling average from the first date in the database based on check out?

       

      Thanks again for the help

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Stan_Lego ;

    I don't quite understand why Averge starts from 0, can you share the results and logic you expect to output?

    RoLL = 
CALCULATE(AVERAGE([CT]),FILTER(ALL(TrackingTable),DATEDIFF([CheckOutTime],MAX([CheckOutTime]),DAY)<=6&&DATEDIFF([CheckOutTime],MAX([CheckOutTime]),DAY)>=0))

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.