SQL query or Dax for Break down running total (cumulative)

[SpiroswayGR]

Dear community members,

I am trying to create a field or measure that will break down the running total field.

For example : 

01/02/2022 : Revenue total = 100

02/02/2022 : Revenue total = 300 (that means 100 from 1st day + 200 2nd day)

03/02/2022 : Revenue total = 800 (that means 100 + 200 + 500 or 300 + 500 of 3rd day)

If I was working in Excel, I could easily create a new field that will deduct 1st day from the 2nd day, 2nd-day revenue from the 3rd day, so I will get the new revenue per day. (300 - 100 = 200 revenue for 2nd day, 800 - 300 = 500 revenue for 3rd day etc.)

Thanks in advance,

Spyros

[serpiva64]

Hi,

to obtain this :

you can:

- add index from 0

- add custom column

- extract values

- replace error with 0

- change type, add custom column

- remove unnecessary columns

and that's done

here the code if you need it

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMjJR0lQwMDpVgdoKARkqARTNAYSdAYJmiCLGgKFIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Range(#"Added Index"[Column2],[Index]-1,1)),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From))}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values1", {{"Custom", "0"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Errors",{{"Custom", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Column2]-[Custom]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
#"Removed Columns"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

and accepting it as a solution !

[amitchandak · Accepted solution]

@SpiroswayGR , a new column

todays revenue =

var _max = maxx(filter(Table, [Date] < earlier([Date]) ), [Date])

var _rev = maxx(filter(Table, [Date] =_max ), [revenue]) 

return

[revenue] -_rev

[SpiroswayGR]

@amitchandak 

Great solution!! Thanks!!

@serpiva64 Your solution is worked too, but generally I prefer variable solutions. Thanks again for your time!

[SpiroswayGR]

@amitchandak 

How can we modify the function if we want to do the same exercise for data that have double dates and extra column country?

For example

Variable solution not working well here.

@serpiva64 but need revision on 1st day for 2nd country

semi working with country

[serpiva64]

Hi,

Before adding index you have to group by

then you add the index

- expand

- add a custom

- then extract values

and apply the necessary steps to get your goal