cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Duration of time in each month

I need to report the amount of equipment downtime in each month, but have an issue when the startDateTime and and EndDateTime are in two different months. I have a seperate date table that is linked to the Startdate

As per the first line example below,  the total downtime is 178 hours between 9pm on the 26th March to 3rd April 7am.

I need a report filtered by month to show 99hours in March and  79hours in April

However, I am currently getting 178hours in March, and nothing in April's report because the start date is in the previous month.

Is anyone able to give me some help

 plant_no *Down Time (DT) *Operating Time (OT) *Operational Delay (OD) *Operational Standby (OS) StartDatetime EndDatetime StartDatetime to EndDatetime* DT305 2.98333333 26/03/2024 21:00 3/04/2024 7:00 178 DT305 1 26/03/2024 20:00 26/03/2024 21:00 1 DT305 1 26/03/2024 19:00 26/03/2024 20:00 1 DT305 2 26/03/2024 17:00 26/03/2024 19:00 2 DT305 2 26/03/2024 15:00 26/03/2024 17:00 2 DT305 1 26/03/2024 14:00 26/03/2024 15:00 1 DT305 2 26/03/2024 12:00 26/03/2024 14:00 2 DT305 7 26/03/2024 5:00 26/03/2024 12:00 7 DT305 9 25/03/2024 20:00 26/03/2024 5:00 9
1 ACCEPTED SOLUTION
Super User

Hi @amprice58 ,

I was abble to understand what was happening we were loosing one day on the change of the date because of the minimun date that gets the 00:00 in time so I had to get those 24 hours back to the calculation also made some optimization of the formula:

``````Downtime Calculation = VAR _temptable = ADDCOLUMNS(
'Table',
"EndDateMonth", MIN(
'Table'[EndDatetime],
MAX('calendar'[Date]) + 1
),
"StartDateMonth", MAX(
'Table'[StartDatetime],
MIN('calendar'[Date])
),
"@DateCheck", IF(
MIN(
'Table'[EndDatetime],
MAX('calendar'[Date]) - 1 / 60 / 60 / 24
) >= MAX(
'Table'[StartDatetime],
MIN('calendar'[Date])
),
1
)
)
VAR _result =
SUMX(
FILTER(
_temptable,
[@DateCheck] = 1
),

DATEDIFF(
[StartDateMonth],
[EndDateMonth],
HOUR
)
)

RETURN
_result``````

See file attached.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

8 REPLIES 8
Regular Visitor
Regular Visitor

Hi MFelix,

thanks for your help with my PBI issue.

I have created a sample report and hope this explains the issue a little further.

Community Support

Hi @amprice58 ,

Thanks for the reply from MFelix .

You will need to start by calculating the daily downtime by breaking down the total downtime into individual days.

Please create a measure to summarize monthly downtime:

``````Monthly Downtime =
SUMX(
FILTER(
'Date',
"DailyDowntime", CALCULATE([Total Downtime], 'YourTable'[StartDatetime] <= 'Date'[Date], 'YourTable'[EndDatetime] >= 'Date'[Date])
),
[DailyDowntime] > 0
),
[DailyDowntime]
)``````

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Super User

Hi @amprice58 ,

Try the following code:

``````Downtime Calculation = VAR _calendar = SELECTCOLUMNS(
FILTER(
CROSSJOIN(
'calendar',
'Table'
),
'calendar'[Date] >= MIN('Table'[StartDatetime]) && 'calendar'[Date] <= MAX('Table'[EndDatetime])
),
'calendar'[Date]
)
VAR _maxdate = TOPN(
1,
_calendar,
'calendar'[Date],
DESC
)
VAR _mindate = TOPN(
1,
_calendar,
'calendar'[Date],
ASC
)
'Table',
"EndDateMonth", IF(
'Table'[EndDatetime] <= MAX('calendar'[Date]),
'Table'[EndDatetime],
MAX('calendar'[Date])
),
"StartSDateMonth", IF(
'Table'[StartDatetime] >= MIN('calendar'[Date]),
'Table'[StartDatetime],
MIN('calendar'[Date])
),
"dd", IF(
'Table'[EndDatetime] <= _mindate,
1
)
)
VAR _result = IF(
SUMX(
_temptalbe,
IF(
[dd] = 1,
DATEDIFF(
'Table'[StartDatetime],
'Table'[EndDatetime],
HOUR
),
DATEDIFF(
[StartSDateMonth],
[EndDateMonth],
HOUR
)
)
) > 0,
SUMX(
_temptalbe,
IF(
[dd] = 1,
DATEDIFF(
'Table'[StartDatetime],
'Table'[EndDatetime],
HOUR
),
DATEDIFF(
[StartSDateMonth],
[EndDateMonth],
HOUR
)
)
)
)
RETURN
_result``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

Hi, thanks heaps for your help!

Its close, just the Downtime Calculation totals are not correct, can I please ask if you can take another look at it please? I have a small example file, but not sure how to link it here

The date entry that has a start date and end date crossing over months, the tables are now splitting the hours into each month, so long as the data table is not joined to the data table.

Super User

Hi @amprice58 ,

I was abble to understand what was happening we were loosing one day on the change of the date because of the minimun date that gets the 00:00 in time so I had to get those 24 hours back to the calculation also made some optimization of the formula:

``````Downtime Calculation = VAR _temptable = ADDCOLUMNS(
'Table',
"EndDateMonth", MIN(
'Table'[EndDatetime],
MAX('calendar'[Date]) + 1
),
"StartDateMonth", MAX(
'Table'[StartDatetime],
MIN('calendar'[Date])
),
"@DateCheck", IF(
MIN(
'Table'[EndDatetime],
MAX('calendar'[Date]) - 1 / 60 / 60 / 24
) >= MAX(
'Table'[StartDatetime],
MIN('calendar'[Date])
),
1
)
)
VAR _result =
SUMX(
FILTER(
_temptable,
[@DateCheck] = 1
),

DATEDIFF(
[StartDateMonth],
[EndDateMonth],
HOUR
)
)

RETURN
_result``````

See file attached.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

Thanks so very much MFlelix for helping me, and finding a solution, I'm so very greatful, thank you!

Super User

Hi @amprice58 ,

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors