Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello everyone,
I have a table that looks like the following: An ID field, a phase, and the date the item was put in that phase (or the phase start date).
I want to add a column that finds the start date of the next phase for that ID. If there is no phase after the current one, the column should say null. I added an example of what that column would look like below.
My ultimate goal is to find how many days are spent in each phase.
This is what I tried that did not work:
phase end date =
VAR CurrentID = SELECTEDVALUE(Table[ID])
VAR CurrentDate = SELECTEDVALUE(Table[Phase Start])
VAR PhaseEndDate = CALCULATE(
MIN(Table[Phase Start]),
FILTER(Table, Table[ID] = CurrentID && Table[Phase Start] > CurrentDate) )
RETURN PhaseEndDate
The above code just returned all blank values.
Thanks!
To get a new column for the next phase time:
next start = CALCULATE(MIN('Table'[Start]), REMOVEFILTERS('Table'[Start]), 'Table'[Start] > EARLIER('Table'[Start]))
Hi,
I get a circular dependency error when I tried this! Any ideas?
Thanks @vicky_ for your concern about this thread.
@asodie If you want a calculated column, you can modify your formula like below. This should work.
phase end date =
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[Phase Start]
VAR PhaseEndDate = CALCULATE(
MIN('Table'[Phase Start]),
FILTER('Table', 'Table'[ID] = CurrentID && 'Table'[Phase Start] > CurrentDate) )
RETURN PhaseEndDate
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
This worked great on my dummy data (thank you!) but when I do it on my real, much larger dataset I get this error:
There's not enough memory to complete this operation. Please try again later when there may be more memory available.
Any ideas how to make it more memory efficient?
Hi @asodie
Not sure if this would be more efficient, you may give it a try:
phase end date =
VAR CurrentPhase = 'Table'[Phase]
RETURN
CALCULATE(MIN('Table'[Phase Start]),ALLEXCEPT('Table','Table'[ID]),'Table'[Phase]>CurrentPhase)
Best Regards,
Jing
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |