Get Current and previous date

[Kish1999]

Helo All,

I Have a requirement where we have to check if a particular date has been changed and if it has been changed then find by how many days it has been changed. The date can be pushed back or borught forward.

The modified date can changed based on some other factors other than date change. Below is a sample data and expected output:

Project	Date1	Modified date
A	01-05-2023	01-05-2023
A	07-05-2023	03-05-2023
A	12-06-2023	06-05-2023
A	12-06-2023	10-05-2023
A	12-06-2023	12-05-2023
B	10-05-2023	02-05-2023
B	21-04-2023	04-05-2023
B	21-04-2023	06-05-2023

Output
Project	CurrDate	PrevDate	Var
A	12-06-2023	07-05-2023	36
B	21-04-2023	10-05-2023	-20

I have written two measures to get the data, but it seems to be giving me the wrong data and i somewhat know why, can someone please help me with this.

CurrDate =

calculate(max(Table[Date1]), all(Table[Date1]), Table[ModifiedDate] = Max(Table[ModifiedDate]))

PrevDate = 

Var MaxDate = Calculate(max(Table[Date1]), all(Table[Date1]))

Var MaxModDate = Calculate(max(ModifiedDate), all(Table[ModifiedDate]))

Var PrevModDate = Calculate(Max(Table[ModifiedDate]), Table[ModifiedDate]<MaxModDate)

Var PrevDate = Calculate(Max(Table[Date1]),  Table[ModifiedDate] = PrevModDate)

Return PrevDate

 Reagrds

[v-jianboli-msft · Accepted solution]

Hi @Kish1999 ,

Please try:

CurrDate = 
var _a = MAX('Table'[Modified date])
return CALCULATE(MAX('Table'[Date1]),FILTER('Table',[Modified date]=_a))

PrevDate = 
var _a = [CurrDate]
var _b = CALCULATE(MIN('Table'[Modified date]),FILTER('Table',[Date1]=_a))
var _c = CALCULATE(MAX('Table'[Modified date]),FILTER('Table',[Modified date]<_b))
return CALCULATE(MAX('Table'[Date1]),FILTER('Table',[Modified date]=_c))

Var = DATEDIFF([PrevDate],[CurrDate],DAY)

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.