Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to utilize DAX to create a table (not power query), to help summarize the turnaround time between to services. Service 1 occures before Service 2. I ultimately want to know the time between the 1st time Service 2 starts for a customer [in a porgram] (minimum) from the most recent time Service 1 stopped (maximum & <Service 2 start). The other thing that must be considered, is the Status of both services must = Completed.
I know I have to start with summarizing the Service2 table, since that dictates if the Program/Customer combination is in the results table. I am currently using: SUMMARIZE(filter(Service2,Service2[Status]="Completed"),Service2[Program],Service2[Customer],"Service2_Start",min(Service2[Start])
However, I don't know how to bring in the maximum stop values from Service1 table that corresponds to the same Program/Customer, where Service 1 has a status = completed, and is less than Service2 Start time for that Program/Customer.
And then, I don't know how to add a calculated column using the datediff dax formula between the 2 time periods.
like that you need?
And if greater than service2[Start], what should be the result?
then that Service1[Stop] should not be considered. Only the max Service1[Stop] prior to Service2[start]. In real world terms, Service 2 can't start until Service 1 is complete, however there can be multiple Serivce 1 encounters that don't have anything to do with Service 2.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhlKkoyP5UeW6piC_?e=4ZChba
thank @Ahmedx for your quick and detailed response. Also, the solution needs to take into consideration that the MAX service1[Stop] is not greater than service2[Start]. How do I encorporate that, another && in the Filter?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |