Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |