Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Project | Origin | Staff | Created Date | Edit Date | Milestone | Value before | Value after |
GIN | David | Mike | 04/09/2021 | 04/12/2021 | Case Open | false | true |
GIN | David | Mike | 04/09/2021 | 04/12/2021 | Completed | ||
KCG | Peter | Jordon | 07/06/2021 | 28/07/2021 | Case Open | false | true |
KCG | Peter | Jordon | 07/06/2021 | 29/07/2021 | File Status | New | Rejected |
PQE | Lilly | MJ | 07/08/2021 | 19/07/2021 | File Status | New | Accepted |
Solved! Go to Solution.
@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())
@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())
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |