Duration between last date and start date of a list

[JoaoMS]

Hi all,

I'm trying to figure out how to calculate the duration between two dates of a serie of registers of a list. This list is related to the dates in which our equiptments are out of service due to maintenance. For example:

PLANT	UNIT	Type	Description	Day
A	G4	MP	Generator 4 Maintenance	20/04/2022
A	G4	MP	Generator 4 Maintenance	21/04/2022
A	G4	MP	Generator 4 Maintenance	22/04/2022
B	G1	MP	Generator 1 Maintenance	20/06/2002
B	G1	MP	Generator 1 Maintenance	21/06/2002
B	G1	MP	Generator 1 Maintenance	22/06/2002
B	G1	MP	Generator 1 Maintenance	23/06/2002
B	G1	MCL	Extension of G1 Maintenance	24/06/2002
B	G1	MCL	Extension of G1 Maintenance	25/06/2002
C	G3	MP	Generator 3 Maintenance	27/08/2022
C	G3	MP	Generator 3 Maintenance	28/08/2022
C	G3	MCL	Extension of G3 Maintenance	29/08/2022

But as you can expect we have a bigger list of registers, and we just want a summary as follow based on the previous list:

We just want a Table visualization in our dashboard but I don't know how to calculate the duration days between the last day and the start day of single maintenante description. I just can have the following table, so I'm missing the last column. Thanks in advance:

Joao

[Shaurya · Accepted solution]

Hi @JoaoMS,

Create a summary table using this formula:

Summary = SUMMARIZE('Table', 'Table'[PLANT], 'Table'[UNIT], 'Table'[Type], 'Table'[Description], "Start Date", MIN('Table'[Day]), "End Date", MAX('Table'[Day]))

Then add this column for duration:

Duration = DATEDIFF(Summary[Start Date],Summary[End Date],DAY) + 1

Mark this post as a solution if that works for you!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI