cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculation of downtime

Good day, everyone.

I have problem with calculation of downtime for furnace. I'll explain to you with an example:
I have downtime that lasts 25 days (from March 3 till March 28). When i use slicer (that connected to dates) to set period, for example, from March 2 till March 29, this downtime counts correctly. But if i use period from March 4 till March 28, or March 3 till March 27 - my downtime disappears from the table, so i can't count correctly downtime for time period. 

I tried to solve this problem by adding new tables/columns for existing table, BUT:
1) Power BI uses a lot of computer resources (like, really, A LOT), therefore computer stops to work properly;

2) Time of new table/column adding is increasing greatly (30-40 minutes for creating 1 column).
I tried to find the way for adding new rows by DAX formulas, but i didn't find the way when i use DirectQuery.
Do you have any suggestions for solving this problem?

Best regards
Alexandr

9 REPLIES 9
Solution Sage
Solution Sage

Of course, the row(s) disappears since you're filtering the fact table on the exact days that you've chosen. I still don't quite get the logic you want to apply. Let's say that for one selected furnace the downtime was from date A to date B (A < B). If you select date A (first slicer) and date B (second slicer), the row, obviously, will be there. Do you want to see the row also when you select dates A' and B' where A<=A'<B'<=B? And you don't want this row to appear when you select A' and B' so that either A'< A or B < B'? Is this the logic you're after?

I need to make next presentation:
We have downtime report in our system, and i need to "duplicate" presentation of this report, but in Power BI.
I'll show you this presentation on screenshots:0001.PNG0002.PNG0003.PNG

We see on this screenshot one downtime, but as you can see if we change check period (red part), downtime will not disappear. It will remain, but it will be cut in end (check blue area).

Super User IV
Super User IV

@AlexandrVPat , What I see in the screenshot is logic that, the date shown on the left top is between the start and end dt(datetime) .  Not the start and end dt is between dates on the right top. Can you share the formula you are using?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I should correct you left top - start date, right top - end date. Pale date - unactive date.
I don't use formulas for now, i connect to database directly for all information i need. For now i try to find the way to "cut" downtime if our report need dates, which intersect with downtime. 

@AlexandrVPat ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Unplanned downtime 12.01.19 00:05:00 12.01.19 00:35:00
Unplanned downtime 12.01.19 00:40:00 12.01.19 00:55:00
Unplanned downtime 12.01.19 01:00:00 12.01.19 01:16:00
Unplanned downtime 12.01.19 01:20:00 12.01.19 01:36:00
Unplanned downtime 12.01.19 01:40:00 12.01.19 01:58:00
Unplanned downtime 12.01.19 02:00:00 12.01.19 02:34:00
Unplanned downtime 12.01.19 02:40:00 12.01.19 02:56:00
Unplanned downtime 12.01.19 03:05:00 12.01.19 03:25:00
Unplanned downtime 12.01.19 03:30:00 12.01.19 04:00:00
Unplanned downtime 12.01.19 04:05:00 12.01.19 04:35:00

 

I apologize for not being able to show data in table format (some errors appears in message form)

As you can, i have type of downtime, start date and end date.

P.S. 1st column - type of downtime, 2nd column - start date, 3rd column - end date. I have other columns, but they are derivatives from 3 main columns.

Super User IV
Super User IV

@AlexandrVPat , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Add +0 to measure and check



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

As you can see in this screenshot, area circled in red marker - start date of downtime. Area circled in blue marker - end date of downtime. I need to count downtime circled in black marker.

0001.PNG

But, if i use another dates (examples in screenshot) - downtime i need disappears.

000000000001.PNG000000000000.PNG

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.