Forum Discussion

bgierwi2's avatar
bgierwi2
Icon for Advocate I rankAdvocate I
8 months ago
Solved

3 Day Rolling Average - DAX

I need to generate a DAX column for a 3 day rolling average of a car count. Where it takes the entry from that day, and then whatever entries there are over the previous 2 days. Averaged and then r...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    8 months ago

    Hi bgierwi2 

     

    I have tested with Calculated column. Can you please try this from the below image

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
8 months ago

Hi bgierwi2 

Assuming you are using a dedicated dates table (which is the best practice), create this measure below:

Roling 3 Days Average = 
VAR _period =
    DATESINPERIOD ( Data[Date], MAX ( 'Dates'[Date] ), -3, DAY )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Dates'[Date] ), [Total Revenue] ),
        _period,
        REMOVEFILTERS ( 'Dates' )
    )

 

REMOVEFILTERS is necessary if the dates table has not been marked as such.

Please see the attached sample pbix.