Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to know how to get the difference between two rows in DAX
This is my sample data, I need to get difference in net sales between each day in a new column not a mesaure to be able to plot it to a line graph.
ID / Date / Time / Net Sales
1a 17-01-2017 3:21:44 PM $ 20.00
1a 17-01-2017 3:21:25 PM $ 0.91
1a 17-01-2017 3:20:37 PM $ 3.18
1b 17-01-2017 3:20:20 PM $ 101.82
1b 17-01-2017 7:47:22 AM $ 101.82
1c 17-01-2017 7:47:07 AM $ 23.64
1d 12-01-2017 2:37:01 PM $ 0.91
1e 07-01-2017 2:17:25 PM $ 9.91
1f 07-01-2017 2:12:24 PM $ 0.91
1g 05-01-2017 9:17:03 AM $ 2.32
1g 05-01-2017 9:16:26 AM $ 2.32
1h 04-01-2017 9:47:08 PM $ 12.01
1h 04-01-2017 9:27:26 PM $ 12.91
1h 04-01-2017 6:52:42 PM $ 12.91
1e 03-01-2017 7:38:55 PM $ 7.91
1e 03-01-2017 6:55:20 PM $ 7.91
1e 03-01-2017 4:09:40 PM $ 7.91
1e 03-01-2017 1:20:57 PM $ 7.91
1f 03-01-2017 12:59:19 PM $ 5.91
Solved! Go to Solution.
I reached to get it as a measure through this
Change = CALCULATE( SUM('TableName'[Net Sales]), FILTER('TableName','TableName'[Date]=MAX('TableName'[Date]) ) )- CALCULATE( SUM('TableName'[Net Sales]), FILTER('TableName','TableName'[Date]=MIN('TableName'[Date])))
But I still can't plot it to a line chart or a waterfall chart (which is the target actually)
I reached to get it as a measure through this
Change = CALCULATE( SUM('TableName'[Net Sales]), FILTER('TableName','TableName'[Date]=MAX('TableName'[Date]) ) )- CALCULATE( SUM('TableName'[Net Sales]), FILTER('TableName','TableName'[Date]=MIN('TableName'[Date])))
But I still can't plot it to a line chart or a waterfall chart (which is the target actually)
Hi @Amratya,
It returns zero when I use your formula as the screenshot below.
You want to calculate the difference in eact day regardless of Id, for example, it should be 101.82(max)-0.91(min) for 2017/1/17, right? If it is, you can use the following formula to calculate a measure.
Change = CALCULATE( MAX('TableName'[Net Sales ]), ALLEXCEPT('TableName',TableName[Date]) ) - CALCULATE( MIN('TableName'[Net Sales ]), ALLEXCEPT('TableName',TableName[Date]))
Create a line chart, select a date as row level, the 'change' measure as value level, please see the screenshot.
In addtion, you want to create a calculated column rath than a measure. So you can create the calculated column using same DAX formula, you will get expected result.
Select the Date column as row level, the calculated column as value level, you also get the desired result.
Please feel free to ask if you have any question.
Best Regards,
Angelia
I think this is not working correctly for me, as you can see in the "calculated column - change" it should display (12-7 = 5) beside the date 4/1 because the difference in netsales between 4/1 and 3/1 is (12-7 = 5) also beside 5/1 is dhould display (2-12 = -10)
The used formula I used in a mesure is working ideally only when using a slicer and select two dates
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |