Repeat number in cumulate sum

[Syndicate_Admin]

Hi Community,

I need your help, how can I repeat the number in a cumulate sum for example:

I have a mother table that count me many incidences per day, and I have a running Totals that cumulate incidences per day; nevertheless, the first day (01/02/2023) I have one incidence in my running total and if a drag the formula per day I have 1 until (05/02/2023).

The question is, how can I repeat the values per day in my running total until I have another inicidence and sum 2 and still repeating with theis new value until a new incidence.

I did it in power query and I did the sum cumulate.

As you can see I did the table with the sum cumulate and the column "ins_no" the number 1500210 have 1 incidence in 04/01/2023 and I have another one in 05/01/2023 with sum cumulate equals 2 but the rest of the days it throw me 0 and it must be 2 until a new incidence.

I post my code that how I did this table.

let
    Origen = Folder.Files("C:OneDrive - SSRR\02. CRA\01. Heavy Users\Proyecto Heavy User\HU_2022\BD_HU\Data"),
    #"C:\Users\OneDrive - SSRR\02  CRA\01  Heavy Users\Proyecto Heavy User\HU_2022\BD_HU\Data\_INPUT_repeated_extraDATA_cInst csv" = Origen{[#"Folder Path"="C:\Users\OneDrive - SSRR\02. CRA\01. Heavy Users\Proyecto Heavy User\HU_2022\BD_HU\Data\",Name="INPUT_repeated_extraDATA_cInst.csv"]}[Content],
    #"CSV importado" = Csv.Document(#"C:\OneDrive - SSRR\02  CRA\01  Heavy Users\Proyecto Heavy User\HU_2022\BD_HU\Data\_INPUT_repeated_extraDATA_cInst csv",[Delimiter=",", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars=true]),
    #"Filas agrupadas" = Table.Group(#"Encabezados promovidos", {"ins_no", "fecha_salto"}, {{"Recuento", each Table.RowCount(_), Int64.Type}}),
    #"Filas agrupadas1" = Table.Group(#"Filas agrupadas", {"ins_no"}, {{"Recuento", each _, type table [ins_no=nullable text, fecha_salto=nullable text, Recuento=number]}}),

RunFuction = (RunTable as table) as table =>
let 
    #"Índice agregado" = Table.AddIndexColumn(RunTable, "Índice", 1, 1, Int64.Type),
    #"Personalizada agregada" = Table.AddColumn(#"Índice agregado", "Acumulado", each List.Sum(List.Range(#"Índice agregado"[Recuento],0,[Índice])))
in 
    #"Personalizada agregada",

RunTotals = Table.TransformColumns( #"Filas agrupadas1", {"Recuento", each RunFuction(_)}),
    #"Se expandió Recuento" = Table.ExpandTableColumn(RunTotals, "Recuento", {"fecha_salto", "Acumulado"}, {"Recuento.fecha_salto", "Recuento.Acumulado"}),
    #"Filas ordenadas" = Table.Sort(#"Se expandió Recuento",{{"ins_no", Order.Ascending}}),
    #"Filas filtradas" = Table.SelectRows(#"Filas ordenadas", each ([ins_no] <> "")),
    #"Filas ordenadas1" = Table.Sort(#"Filas filtradas",{{"Recuento.fecha_salto", Order.Ascending}}),
    #"Columna dinamizada" = Table.Pivot(#"Filas ordenadas1", List.Distinct(#"Filas ordenadas1"[Recuento.fecha_salto]), "Recuento.fecha_salto", "Recuento.Acumulado", List.Sum)
in
    #"Columna dinamizada"

Hope you can help me.

Best regards.

[amitchandak]

@Syndicate_Admin , You should unpivot this data. and then create a cumulative measure with help from separate date table(create date from month)

And Create Matrix visual and use month from date table on column(date table and original table should join on date , 1-M , single directional)

https://radacad.com/pivot-and-unpivot-with-power-bi

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc