Calculated column to show difference value btw rows

[Joey2022]

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table
2. To your visual, drag Date from the Calendar Table.
3. Write these measures:

Sales = sum(Data[SaleAmount])

Previous day sales = calculate([Sales],previousday(Calendar[Date]))

Delta = [Sales]-[Previous day sales]

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table
2. To your visual, drag Date from the Calendar Table.
3. Write these measures:

Sales = sum(Data[SaleAmount])

Previous day sales = calculate([Sales],previousday(Calendar[Date]))

Delta = [Sales]-[Previous day sales]

Hope this helps.

[Joey2022]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

[Joey2022]

Hi Ashish Mathur,

Thank you for working it out. I still digest your DAX code, not really understand about how to calculate the Previous day and the Sales in the day.

I am trying with my data. It's like a blind puzzle haha doesn't work out as expected.

Joey

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark it as Answer.

[Joey2022]

Thank you Ashish Mathur.

It really worked the way I expect. I am really appreciate your guide. Very gentle help.

Joey

[Ashish_Mathur]

You are welcome.

[Vijay_A_Verma]

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"

[Joey2022]

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