Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnnyK
Advocate II
Advocate II

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
JohnnyK
Advocate II
Advocate II

@amitchandak, fantastic! thank you for sharing of knowledge!

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.