Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

Calculate difference between previous date of same column using DAX

Hi All, I have a requirement to calculate the difference between date of the current line to the previous line of the same column. Please find sample data below: Protocol Received Status ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi POSPOS 

    You can add a index column in power query first and apply it

    Then create a calculated column

    Column = var a=FILTER('Table',[Protocol]=EARLIER('Table'[Protocol]))
var b=MINX(FILTER(a,[Date]=MINX(a,[Date])),[Index])
return IF([Index]=b,DATEDIFF([Received],[Date],DAY),DATEDIFF(MAXX(FILTER(a,[Index]=EARLIER('Table'[Index])-1),[Date]),[Date],DAY))

    Output

    Best Regards!

    Yolo Zhu

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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi POSPOS 

You can add a index column in power query first and apply it

Then create a calculated column

Column = var a=FILTER('Table',[Protocol]=EARLIER('Table'[Protocol]))
var b=MINX(FILTER(a,[Date]=MINX(a,[Date])),[Index])
return IF([Index]=b,DATEDIFF([Received],[Date],DAY),DATEDIFF(MAXX(FILTER(a,[Index]=EARLIER('Table'[Index])-1),[Date]),[Date],DAY))

Output

Best Regards!

Yolo Zhu

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