Create Table with Escalating Values

[bgierwi2]

I need to create a table where one column goes 1 to 1000

The other column starts at 500 for the first 2 entries, and then increases by 500 and stays for the next 3 entries, and that continues for the rest of the table.

Occurrence	Penalty
1	500
2	500
3	1000
4	1000
5	1000
6	1500
7	1500
8	1500
9	2000
10	2000
11	2000
12	2500
13	2500
14	2500

and continuing to add 500 for the next 3 until the table ends

[bgierwi2]

@AshokKunwar 

That worked perfectly for DAX.

Thank you

How similiar would the code be if I wanted to do it in Power Query?

[AshokKunwar]

@bgierwi2

 Your welcome,

If you have any more questions then you free to ask 

[AshokKunwar · Accepted solution]

Hii @bgierwi2 

You need a sequence where the "Penalty" increment happens at irregular intervals:

• ​Step 1: Occurrences 1–2 (2 items) = 500
• ​Step 2: Occurrences 3–5 (3 items) = 1000
• ​Step 3: Occurrences 6–8 (3 items) = 1500
• ​And so on...

To Fix It

Go to the Modeling tab, click New Table, and paste the following code:

PenaltyTable = 
VAR BaseTable = GENERATESERIES(1, 1000, 1) -- Creates Occurrence 1 to 1000
RETURN
SELECTCOLUMNS(
    BaseTable,
    "Occurrence", [Value],
    "Penalty", 
        VAR _ID = [Value]
        RETURN
        IF(
            _ID <= 2, 
            500, 
            -- This logic handles the "every 3 entries" shift after the first 2
            500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500
        )
)

Summary for the Community

​By using INT(DIVIDE([Value] - Offset, Step)), you can create custom "stepped" sequences that don't follow a standard 1:1 increment.

​If this DAX table correctly generates your penalty schedule, please mark this as the "Accepted Solution"!