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.
Hello, guys!
I need help to calculate a range of days for combining some of these statuses.
As an example:
-ID 8359: The interval between the "new" and "prioritized" must be 37 days;
-ID 8359: The interval between the "approved" and "prioritized" must be 1 day;
-ID 8490: The interval between the "approved" and "prioritized" must be 21 days;
8359 | 04/09/2019 | New |
8359 | 10/10/2019 | Approved |
8359 | 11/10/2019 | Prioritized |
8359 | 14/10/2019 | In Progress |
8359 | 30/10/2019 | Done |
8490 | 11/09/2019 | New |
8490 | 10/10/2019 | Approved |
8490 | 31/10/2019 | Prioritized |
8490 | 04/11/2019 | In Progress |
Solved! Go to Solution.
Hi @Anonymous ,
Please try this formula.
Measure = AVERAGEX(VALUES('table'[ID]),'table'[interval Approved & Prioritized])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please reference my sample to have a try.
New = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "New" ))
interval New & Prioritized =
var b = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Prioritized" ))
return
DATEDIFF([New],b,DAY)
Approved = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Approved" ))
interval Approved & Prioritized =
var b = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Prioritized" ))
return
DATEDIFF([Approved],b,DAY)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What if I wanted to have an average by status after calculating the day difference of all IDs? In this case for example: approved & prioritized = (1+21)/2 = 11.
@v-xuding-msft : Do we need to use Group by or CURRENTGROUP() in this context?
What if one of the status/date is not exist ?
create measures like this.
new date = calculate(min('table'[Admission date ]), status = "new")
prioritized date= calculate( max('table'[Discharge Date]), status = "new")
date diff =
Avergae(
VALUES(table[ID])
, datediff( new date,Min prioritized date,days)
)
You will diff at your table id level. And post that you can take action. On top of datediff you can put if condition
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
What if I wanted to have an average by status after calculating the day difference of all IDs? In this case for example: approved & prioritized = (1+21)/2 = 11.
Hi @Anonymous ,
Please try this formula.
Measure = AVERAGEX(VALUES('table'[ID]),'table'[interval Approved & Prioritized])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |