Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to Solution.
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
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.
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(
ADDCOLUMNS(
'Date',
"DailyDowntime", CALCULATE([Total Downtime], 'YourTable'[StartDatetime] <= 'Date'[Date], 'YourTable'[EndDatetime] >= 'Date'[Date])
),
[DailyDowntime] > 0
),
[DailyDowntime]
)
You can upload your pbix files to Onedrive, Googledrive, or Dropbox. Remember not to log in to your account in Power BI Desktop when uploading.
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!
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
)
VAR _temptalbe = ADDCOLUMNS(
'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êsHi, 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.
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êsThanks so very much MFlelix for helping me, and finding a solution, I'm so very greatful, thank you!
Hi @amprice58 ,
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
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êsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |