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

     

     

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

Please try the measure below:

 

3 Day Rolling Avg  =
VAR CurrentDate = SELECTEDVALUE ( Cars[Date] )
VAR Window =
    FILTER (
        ALL ( Cars ),
        Cars[Date] <= CurrentDate
            && Cars[Date] > CurrentDate - 3
    )
RETURN
ROUNDDOWN (
    AVERAGEX ( Window, CALCULATE ( SUM ( Cars[Car Count] ) ) ),
    0
)

 

If you have a proper Calendar table, you can use that in your measure instead.

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

cengizhanarslan 

 

 

I am getting an error on the variable "Window" saying the parameter is not the correct type.

The "Count Date" is formatted as a date.

The "Total Cars Actual" (the value I am averaging) is formatted as a decimal number that isn't summarized.

"Ineos Count" is the data source

 

Is there something I need to change?