Expanding the a status column value based on the start and end time

[mhdtra]

Hi, 

I am new to PowerBI, I have a table containing 4 columns equipmentName, equimentStatus (enumeration), equipment in use (binary), and start and endtime (date). the objective is to create a table to track the status of the equipment on an hourly basis per day (the table will have 24 columns representing each hour of the day and the rows represent the list of the equipment, how to populate the table with the status based on the start and end time. the end goal is to obtain a heatmap table to display the status of the equipment hourly during a day based on the events table.  what is the best approach?

Thanks in advance for your support.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[mhdtra]

Hello,

Below a sample of the status change events table and the desired status chart per machine

Thanks
Sample Data (Assetstatus change event with start and endtime)

Daily Status chart per machine

[lbendlin]

Please provide sample data in usable format.  

[mhdtra]

Name	AssetStatus	StartDateTime	EndDateTime
CCA04	En panne	8/27/2022 10:00:00 AM	8/27/2022 12:00:00 PM
CCA04	Disponible	8/26/2022 12:00:00 AM	8/27/2022 10:00:00 AM
CCA04	Disponible	8/27/2022 12:00:00 PM	9/1/2022 12:00:00 AM
CNA6	En panne	8/27/2022 8:00:00 AM	8/27/2022 12:00:00 PM
CNA6	Disponible	8/26/2022 12:00:00 AM	8/27/2022 8:00:00 AM
CNA6	Disponible	8/27/2022 12:00:00 PM	9/1/2022 12:00:00 AM

[lbendlin · Accepted solution]

Here's a rather feeble attempt to do this with a standard visual.  Might be better off looking for a custom visual.

[mhdtra]

thanks you lbendlin, this should put me on the right track!