Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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 !
@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
Great solution!! Thanks!!
@serpiva64 Your solution is worked too, but generally I prefer variable solutions. Thanks again for your time!
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |