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