Forum Discussion

Kish1999's avatar
Kish1999
Helper II
3 years ago
Solved

Get Current and previous date

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:

ProjectDate1Modified date
   
A01-05-202301-05-2023
A07-05-202303-05-2023
A12-06-202306-05-2023
A12-06-202310-05-2023
A12-06-202312-05-2023
B10-05-202302-05-2023
B21-04-202304-05-2023
B21-04-202306-05-2023

 

Output   
ProjectCurrDatePrevDateVar
A12-06-202307-05-202336
B21-04-202310-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
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.