cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Repeat number in cumulate sum

Hi Community,

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

diegoa2f_1-1675379421079.png

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.

diegoa2f_2-1675380013605.png

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.

1 REPLY 1
amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors