Lead of MAx Date

[Anonymous]

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

[v-danhe-msft · Accepted 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

[Seward12533]

Look into earlier to retrieve previous values. There are lots of examples in the forum here is one.
https://community.powerbi.com/t5/Desktop/Previous-date-values/td-p/134347