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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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?

Anonymous
Not applicable

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

amitchandak
Super User
Super User

@Anonymous , 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?

Anonymous
Not applicable

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. 

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

amitchandak
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors