Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Resolver III
4 years ago
Solved

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

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

serpiva64's avatar
serpiva64
Solution Sage
4 years ago

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 !