Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
In the below table we have total GLP and month. Total GLP is aggregated value for the month of Aug-13 and so on.
Want a column with the difference between Total GLP value based on months. In excel we could have achieved this C2 = b3 - b2 for the distinct value of months. Can someone help me how to write a calculated column in DAX
Month | Total GLP |
Aug-13 | 5,07,73,678 |
Aug-13 | 5,07,73,678 |
Sep-13 | 6,24,10,183 |
Sep-13 | 6,24,10,183 |
Sep-13 | 6,24,10,183 |
Oct-13 | 7,14,50,816 |
Oct-13 | 7,14,50,816 |
Oct-13 | 7,14,50,816 |
Oct-13 | 7,14,50,816 |
Oct-13 | 7,14,50,816 |
Oct-13 | 7,14,50,816 |
Nov-13 | 8,36,69,540 |
Nov-13 | 8,36,69,540 |
Nov-13 | 8,36,69,540 |
Nov-13 | 8,36,69,540 |
expected output is
Month | Total GLP |
Aug-13 | 1,16,36,505 |
Aug-13 | - |
Sep-13 | - |
Sep-13 | 90,40,633 |
Sep-13 | - |
Oct-13 | - |
Oct-13 | - |
Oct-13 | - |
Oct-13 | - |
Oct-13 | 1,22,18,724 |
Oct-13 | - |
Nov-13 | - |
Nov-13 | - |
Nov-13 | - |
Nov-13 | 1,87,40,132 |
Nov-13 | - |
Solved! Go to Solution.
Hi @rgsingh123,
The [Month] column should be set to date type. In Query Editor mode, add an index column.
Create a calculated column with below formula.
Difference = IF ( 'Monthly GLP'[Index] = CALCULATE ( MIN ( 'Monthly GLP'[Index] ), ALLEXCEPT ( 'Monthly GLP', 'Monthly GLP'[Month] ) ), CALCULATE ( MAX ( 'Monthly GLP'[Total GLP] ), FILTER ( 'Monthly GLP', 'Monthly GLP'[Month].[MonthNo] = EARLIER ( 'Monthly GLP'[Month].[MonthNo] ) + 1 ) ) - 'Monthly GLP'[Total GLP], BLANK () )
Best regards,
Yuliana Gu
Thanks it worked.
I feel it's a little complex to write such big DAX queries for simple calculations like this. Do you recommend any good tutorial for DAX that will help me to master this?
Hi @rgsingh123,
The [Month] column should be set to date type. In Query Editor mode, add an index column.
Create a calculated column with below formula.
Difference = IF ( 'Monthly GLP'[Index] = CALCULATE ( MIN ( 'Monthly GLP'[Index] ), ALLEXCEPT ( 'Monthly GLP', 'Monthly GLP'[Month] ) ), CALCULATE ( MAX ( 'Monthly GLP'[Total GLP] ), FILTER ( 'Monthly GLP', 'Monthly GLP'[Month].[MonthNo] = EARLIER ( 'Monthly GLP'[Month].[MonthNo] ) + 1 ) ) - 'Monthly GLP'[Total GLP], BLANK () )
Best regards,
Yuliana Gu
Thanks it worked.
I feel it's a little complex to write such big DAX queries for simple calculations like this. Do you recommend any good tutorial for DAX that will help me to master this?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |