Forum Discussion
Calculated column to show difference value btw rows
Hi there!
I need a favor from you guys, experts. I stucked in the matter for weeks. Pls kindly help me out.
The matter is:
I want to find the difference value of sale btw 2 days as:
| Date | SaleAmount | DifferenceSA |
| D1 | Sum1 | |
| D2 | Sum2 | Sum2-Sum1 |
| D3 | Sum3 | Sum3-Sum2 |
The input Data looked like:
| Date | SaleAmount |
| D1 | S1 |
| D1 | S2 |
| D2 | S3 |
| D2 | S4 |
| D2 | S5 |
| D3 | S6 |
Joey
Hi,
Try this
- Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table
- To your visual, drag Date from the Calendar Table.
- Write these measures:
Sales = sum(Data[SaleAmount])
Previous day sales = calculate([Sales],previousday(Calendar[Date]))
Delta = [Sales]-[Previous day sales]
Hope this helps.
9 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjFU0lEyNDBQitWBcoxMoRwjkIwxEsfIAJkDU2YM5JiAZGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SaleAmount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"SaleAmount", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"SaleAmount", each List.Sum([SaleAmount]), type nullable text}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "DifferenceSA", each try [SaleAmount]-#"Added Index"[SaleAmount]{[Index]-1} otherwise null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"Thank you Verma!
Got your idea with power querry. But seem my data set is quite large for the group row. I would prefer some solution go with DAX
Joey
Hi,
Try this
- Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table
- To your visual, drag Date from the Calendar Table.
- Write these measures:
Sales = sum(Data[SaleAmount])
Previous day sales = calculate([Sales],previousday(Calendar[Date]))
Delta = [Sales]-[Previous day sales]
Hope this helps.
Thank you Ashish Mathur.
It really worked the way I expect. I am really appreciate your guide. Very gentle help.
Joey
You are welcome.
hi Ashish_Mathur,
I still have a issue regarding to the day without data e.g Sunday. I want to find the different btw Monday and last Saturday. So the "previousday" don't work properly in the case.
Thank you
Joey
