Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have a data table, that works on Slowly Changing Dimensions principle. It generates a new row whenever Bigger changes with that row have been made. I am interested in only one of these parameters - "stage". I want to calculate time, that each task has spent at different stages. This is a small example table:
Task_ID | Stage | Start_date | End_date |
111 | A | 12.07.2019 | 15.07.2019 |
111 | A | 12.07.2019 | 22.07.2019 |
111 | B | 22.07.2019 | 25.07.2019 |
111 | A | 25.07.2019 | 27.07.2019 |
111 | C | 27.07.2019 | 29.07.2019 |
The problem is, that some of these rows have dates, that overlap based on other factors. But different stages never overlap, because each task at any given moment can be only in one stage.
What could be the best way to calculate, that task 111 spent 12 days at stage A? (10 days from 12.07 to 22.07, and 2 days from 25.07 to 27.07).
Thank you in advance! 🙂
Solved! Go to Solution.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
So I found out that all of the overlapping rows had one common feature. So I could use it to filter down those rows in Query Editor. After that I added calculated column, that calculated time spent in that state. For that I used DATEDIFF:
Time_spent = DATEDIFF(Table[Start_date]; Table[End_date];DAY)
After that I needed mesure, to count total time spent in each Stage. I was interested in only 3 stages so I created separate Mesure for each of them.
Time_stage_A=CALCULATE(SUM(Table[Time_spent]);FILTER(Table;Table[STAGE]="A"))
I later modified this Mesure to give me Average time spent. It gave me same result, when used in table, where it calculated result for each task, but it also worked in visualizations, where I needed average time for group of tasks.
Average_time_stage_A=DIVIDE(CALCULATE(SUM(Table[Time_spent]);FILTER(Table;Table[STAGE]="A"));DISTINCTCOUNT(Table;Table[Task_ID]))
There surely was a better way to archive this (like solution from @Greg_Deckler post, which I will mark as a Solution), but for now, my mesure will work fine.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
From what I understand, your example doesn't have overlaping data - so I'm not shure it will work for me. But please correct me if I'm wrong
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |