Duration of time in each month

[amprice58]

 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

[amprice58]

https://mtgibsoniron-my.sharepoint.com/:u:/g/personal/alan_price_mgx_com_au/EbBwBqcz6OJBumnXjRzYgOoB... 

[amprice58]

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.

https://mtgibsoniron-my.sharepoint.com/:u:/g/personal/alan_price_mgx_com_au/EbBwBqcz6OJBumnXjRzYgOoB... 

[v-huijiey-msft]

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!

[MFelix]

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

[amprice58]

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.

[MFelix · Accepted 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.

[amprice58]

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

[MFelix]

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.