Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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