Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling 7-Day average calculation.

I am trying to get a rolling 7 day average of a value field.  I seem to be having some issues and need some suggestions.  Thanks for any input. Current Output:   Current Measures:   Load t...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Make sure year, month, and day are coming from date table(in visual)  and date table is marked as date table

     

    Try with small change

    7 Day Avg V3 =
    CALCULATE (
    AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),
    DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),
    Dates[Date] < TODAY ()
    )
     
    Rolling Days Formula: https://youtu.be/cJVj5nhkKBw
amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try like assume you have measure Rolling Load to Store Avg or the sum measure Rolling Load to Store

Try a new measure like 

 

CALCULATE (

AvergaeX(values(Dates[Date]) , [Rolling Load to Store Avg] ) ,

DATESINPERIOD (
Dates[Date],
LASTDATE ( Dates[Date] ),
-7,
DAY
),
FILTER (Dates, Dates[Date] < TODAY ())
)

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the quick reply.  I have created a new measure(7 Day Avg V3) with your dax recommendations.  It seems to be mirroring the "Load to Store Avg" calc.  Any suggestions? 

7 Day Avg V3 =
CALCULATE (
AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),
FILTER ( Dates, Dates[Date] < TODAY () )
)

Rolling Load to Store Avg =
    AVERAGEX(
FILTER(ALLSELECTED(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])),
[Load to Store Avg]
)

Load to Store Avg =
AVERAGEX('Load to Store','Load to Store'[value])
 




 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Make sure year, month, and day are coming from date table(in visual)  and date table is marked as date table

     

    Try with small change

    7 Day Avg V3 =
    CALCULATE (
    AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),
    DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),
    Dates[Date] < TODAY ()
    )
     
    Rolling Days Formula: https://youtu.be/cJVj5nhkKBw
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Great that seems to fix the issue.  I will select this as the solution.  I have one more questions regarding this calc.  I am needing to create a seperate measure that will populate based on you're supplied measure calc.  I'm hoping you can help out with how to add a measure as filter context to a seperate measure?  Below is the "ask".


      Process Orders 7 Day Avg =

      CALCULATE (

          AVERAGEX (

              VALUES ( 'Process Orders'[Inspection Point Date] ),

              [Rolling Process Orders Avg]

          ),

          DATESINPERIOD (

              'Process Orders'[Inspection Point Date],

              LASTDATE ( 'Process Orders'[Inspection Point Date] ),

              -7,

              DAY

          ),

          'Process Orders'[Inspection Point Date]

              < TODAY ()

      )

      I am needing to create a calculation that using the structure of the below filter context.  The calc below with work without errors but is the filter context used correctly with this method?  Thanks!

      New_Calc =

      Switch(

            True(), [Process Orders 7 Day Avg] > 1, 1,

                         [Process Orders 7 Day Avg] >0,5,

                          0

      )