Calculate total time spent in each stage (Slowly Changing Dimensions)

[Raivo_S]

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! 🙂

[Raivo_S]

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.

[Greg_Deckler · Accepted 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...

[Raivo_S]

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