Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SpiroswayGR
Resolver III
Resolver III

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

5 REPLIES 5
serpiva64
Super User
Super User

Hi,

to obtain this :

serpiva64_0-1645001786193.png

you can:

- add index from 0

- add custom column

serpiva64_1-1645001868038.png

- extract values

serpiva64_2-1645001917511.png

- replace error with 0

- change type, add custom column

serpiva64_3-1645002005503.png

- 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
Super User
Super User

@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

@amitchandak 

Great solution!! Thanks!!

 

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

@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

SpiroswayGR_0-1645004279309.pngSpiroswayGR_1-1645004297656.png

 

Variable solution not working well here.

 

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

semi working with country

SpiroswayGR_0-1645004650031.png

 

Hi,

serpiva64_0-1645006072460.png

Before adding index you have to group by

serpiva64_1-1645006147539.png

then you add the index

serpiva64_2-1645006203069.png

- expand

serpiva64_3-1645006229209.png

- add a custom

serpiva64_4-1645006261223.png

- then extract values

serpiva64_5-1645006304784.png

and apply the necessary steps to get your goal

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.