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 is (using the Edit Date column): 29/07/2021 - 28/07/2021 = 1 day

 

In PowerBI, what is the good way to do so? Would there be a way to restructure the data, a formula’s better?

 

(note: the data has hundreds of rows, that not each Project has the row of "Case Open" (false to true) and File Status" Rejected.)

 

Thank you.

 

ProjectOriginStaffCreated DateEdit DateMilestoneValue beforeValue after
GINDavidMike04/09/202104/12/2021Case Openfalsetrue
GINDavidMike04/09/202104/12/2021Completed  
KCGPeterJordon07/06/202128/07/2021Case Openfalsetrue
KCGPeterJordon07/06/202129/07/2021File StatusNewRejected
PQELillyMJ07/08/202119/07/2021File StatusNewAccepted
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())

2 Replies

  • 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())