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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

5 REPLIES 5
serpiva64
Solution Sage
Solution Sage

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.