Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This question is two-fold:
I have a list of projects, each with several milestones and associated dates. See an example of one project below that has milestones ("Action Title") and milestone dates "Deadline"
Is there a way to create a function that returns the next date in a list of dates? Then, once you have that, is there a way to return the milestone that is associated with that date? For example in the data set above, is there a way to create two separate functions that:
a) Return 3.29.20 as the next date
b) Return "DG3" as next milestone
I'm trying to create a gantt chart using this data since Power BI doesn't currently support showing multiple milestones along a single project row.
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you should be able to use the formulas below to create two measures to get next date and milestone for an Id.
Next Date = var currentDate = TODAY() var currentID = MAX(Table1[Id]) return CALCULATE(MIN(Table1[Deadline]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))
Next Milestone = var currentDate = TODAY() var currentID = MAX(Table1[Id]) return CALCULATE(MIN(Table1[Action Title]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))
Here is the same pbix file for your reference.
Regards
Hi @Anonymous,
Based on my test, you should be able to use the formulas below to create two measures to get next date and milestone for an Id.
Next Date = var currentDate = TODAY() var currentID = MAX(Table1[Id]) return CALCULATE(MIN(Table1[Deadline]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))
Next Milestone = var currentDate = TODAY() var currentID = MAX(Table1[Id]) return CALCULATE(MIN(Table1[Action Title]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))
Here is the same pbix file for your reference.
Regards
thanks a lot - that worked!
Now for the tricky part - how can I show this in a gantt chart? When I try to calculate duration until milestone by just taking the difference between Next MS Date and TODAY(), the return value is in date format. Is there any way to get this into a number format so that I can use it as a duration in the gantt?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |