Forum Discussion

JohnnyK's avatar
JohnnyK
Icon for Advocate II rankAdvocate II
5 years ago
Solved

To calculate dates difference in the same column, where criteria fits

Good day!   Data as below.   I want to calculate, for each Project, the difference in days from a "Case Open" (false to true) to File Status" Rejected. In the example, Project KCG’s calculation ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    JohnnyK , Create a new column like


    new column =
    var _1 = maxx(filter(Table, [Edit Date] <earlier([Edit Date]) && [Project] =earlier([Project]) && [Milestone] ="Case Open"),[Edit Date])
    return
    if(not(isblank(_1)) && [Value after] ="Rejected", datediff(_1, [Edit Date],day), blank())

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

JohnnyK , Create a new column like


new column =
var _1 = maxx(filter(Table, [Edit Date] <earlier([Edit Date]) && [Project] =earlier([Project]) && [Milestone] ="Case Open"),[Edit Date])
return
if(not(isblank(_1)) && [Value after] ="Rejected", datediff(_1, [Edit Date],day), blank())