3 Day Rolling Average - DAX

[bgierwi2]

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 rounded down.

Date	Car Count	3 Day Rolling Average
1/4/2026	10	4
1/3/2026	1	3
1/2/2026	1	4
1/1/2026	7	7

@AshokKunwar  - You had helped with other issues in the same setup.  Everything else worked, this is the last detail.

[AshokKunwar]

Hii @bgierwi2 

If this solution worked for you, please Mark as Solution! It helps others in the community find this answer more easily. Cheers!

[Breezhall]

3 Day Rolling Average：=
VAR CurrentDate = MAX('表1'[date])
RETURN
AVERAGEX(
    FILTER(
       ALL('表1'),
        '表1'[date] >= CurrentDate -2 && '表1'[date] <= CurrentDate
    ),
    '表1'[count]
)

[AshokKunwar]

To create the table with Occurrence (1 to 1000) and the Stepped Penalty (500, 1000, etc.), follow these steps:

​The Power Query (M) Solution

1. ​In Power BI Desktop, go to Home > Transform Data.
2. ​Go to Home > New Source > Blank Query.
3. ​Open the Advanced Editor and paste the following code:

​<!-- end list -->

let
    // 1. Generate a list from 1 to 1000
    Source = {1..1000},
    
    // 2. Convert that list into a table
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // 3. Rename the column to Occurrence
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Occurrence"}}),
    
    // 4. Change type to Int64
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Occurrence", Int64.Type}}),
    
    // 5. Add the Custom Penalty Logic
    #"Added Penalty" = Table.AddColumn(#"Changed Type", "Penalty", each 
        if [Occurrence] <= 2 then 500 
        else 500 + (Number.IntegerDivide([Occurrence] - 3, 3) + 1) * 500
    ),
    
    // 6. Set Currency Type
    #"Final Type" = Table.TransformColumnTypes(#"Added Penalty",{{"Penalty", Currency.Type}})
in
    #"Final Type"

Summary for the Community

​Using Number.IntegerDivide in Power Query allows you to create complex, non-linear sequences that are baked into your data schema. This keeps your DAX measures clean and focused solely on calculations rather than data generation.

​If this Power Query script successfully builds your 1000-row penalty table, please mark this as the "Accepted Solution"!

[krishnakanth240 · Accepted solution]

Hi @bgierwi2 

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

[bgierwi2]

@krishnakanth240 

That worked!

Thank you!!!

[danextian]

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.

[cengizhanarslan]

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]

@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?