Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver 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 (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

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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

      • SpiroswayGR's avatar
        SpiroswayGR
        Icon for Resolver III rankResolver III
        4 years ago

        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's avatar
    serpiva64
    Icon for Solution Sage rankSolution 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 !