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.
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?
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |