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

 



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors