3 Day Rolling Average - DAX

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"!

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.

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.

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

Hii @bgierwi2 

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