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.
Helo All,
My Data is like this and I need DAX calculation for below :
ID | Work Comletion | LD1 | IS3 | is4 | Value | Date |
2.1 | Stratification | WorkDown2011 | NULL | NULL | 322 | 3/1/2018 |
2.1 | Stratification | WorkDown2012 | NULL | NULL | 288 | 4/1/2018 |
2.1 | Stratification | WorkDown2013 | NULL | NULL | 288 | 5/1/2018 |
2.1 | Stratification | WorkDown2014 | NULL | NULL | 155 | 6/1/2018 |
2.1 | Stratification | WorkDown2015 | NULL | NULL | 142 | 7/1/2018 |
In the above table max(Date)=7/1/2018 & value=142 and previous month=6/1/2018 and value =155, i need previous month row value and max(Date row value) ina single line, something like below
ID | Work Comletion | LD1 | IS3 | is4 | Value | Date | Lead of Previosu month |
2.1 | Stratification | WorkDown2011 | NULL | NULL | 322 | 3/1/2018 | null |
2.1 | Stratification | WorkDown2012 | NULL | NULL | 288 | 4/1/2018 | null |
2.1 | Stratification | WorkDown2013 | NULL | NULL | 288 | 5/1/2018 | null |
2.1 | Stratification | WorkDown2014 | NULL | NULL | 155 | 6/1/2018 | null |
2.1 | Stratification | WorkDown2015 | NULL | NULL | 142 | 7/1/2018 | 155 |
Please let me know if you need more info thanks
Shawn
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you could refer to below steps:
Sample data:
Create a calculated column:
Last value = var a=DATE(YEAR('Table1'[Date]),MONTH('Table1'[Date])-1,DAY('Table1'[Date])) var b=CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date]=a)) return IF([Date]=MAXX('Table1','Table1'[Date]),b,BLANK())
Now you could see the correct result:
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/dje61d7qncopqyu/Lead%20of%20MAx%20Date.pbix?dl=0
Regards,
Daniel He
Hi @Anonymous,
Based on my test, you could refer to below steps:
Sample data:
Create a calculated column:
Last value = var a=DATE(YEAR('Table1'[Date]),MONTH('Table1'[Date])-1,DAY('Table1'[Date])) var b=CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date]=a)) return IF([Date]=MAXX('Table1','Table1'[Date]),b,BLANK())
Now you could see the correct result:
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/dje61d7qncopqyu/Lead%20of%20MAx%20Date.pbix?dl=0
Regards,
Daniel He
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 |
---|---|
98 | |
98 | |
80 | |
76 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |