Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Raivo_S
Frequent Visitor

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

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_IDStageStart_dateEnd_date
111A12.07.201915.07.2019
111A12.07.201922.07.2019
111B22.07.201925.07.2019
111A25.07.201927.07.2019
111C27.07.201929.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! 🙂

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Raivo_S
Frequent Visitor

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
Super User
Super User

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...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.