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

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

Reply
amprice58
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)StartDatetimeEndDatetimeStartDatetime to EndDatetime*
DT3052.98333333   26/03/2024 21:003/04/2024 7:00178
DT3051   26/03/2024 20:0026/03/2024 21:001
DT3051   26/03/2024 19:0026/03/2024 20:001
DT3052   26/03/2024 17:0026/03/2024 19:002
DT3052   26/03/2024 15:0026/03/2024 17:002
DT3051   26/03/2024 14:0026/03/2024 15:001
DT3052   26/03/2024 12:0026/03/2024 14:002
DT3057   26/03/2024 5:0026/03/2024 12:007
DT3059   25/03/2024 20:0026/03/2024 5:009
1 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

 

MFelix_0-1712941906412.png

See file attached.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
amprice58
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.

 

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

v-huijiey-msft
Community Support
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(
        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
Super User
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
	)
	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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

March Example Report.jpgApril Example Report.jpg

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

 

MFelix_0-1712941906412.png

See file attached.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.