Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following table "Vessel schedule":
Vessel - Project - Start date - End date
A - Project1 - 01/02/2019 - 28/06/2019
A - Project 2 - 01/09/2019 - 01/06/2020
B - Project1 - 15/02/2019 - 15/05/2019
...
I need to know the utilization rate of each vessel in a period defined by a slicer.
If the slicer period is 01/01/2019 - 31/12/2019, the result should be almost like that:
Vessel - nb of days utilization - utilization rate
A - 270 - 75%
B - 90 - 25%
Thanks for your help
Solved! Go to Solution.
Hi @azizdoghmi
Let me know if you'd like to get below results:
1. Created a calendar table as slicer:
SlicerTable = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))
2. Add below measures:
Period = DATEDIFF(MIN(SlicerTable[Date]),MAX(SlicerTable[Date]),DAY)+1
days utilization = var a = MAX(MAX('Table'[Start date]),MIN(SlicerTable[Date]))
var b = MIN(MAX('Table'[End date]),MAX(SlicerTable[Date]))
Return
DATEDIFF(a,b,DAY)+1
nb of days utilization = SUMX(SUMMARIZE(FILTER(ALLSELECTED('Table'),'Table'[Vessel]=MAX('Table'[Vessel])),'Table'[Project],"p",[days utilization]),[p])
Pbix attached.
Hi @azizdoghmi
Let me know if you'd like to get below results:
1. Created a calendar table as slicer:
SlicerTable = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))
2. Add below measures:
Period = DATEDIFF(MIN(SlicerTable[Date]),MAX(SlicerTable[Date]),DAY)+1
days utilization = var a = MAX(MAX('Table'[Start date]),MIN(SlicerTable[Date]))
var b = MIN(MAX('Table'[End date]),MAX(SlicerTable[Date]))
Return
DATEDIFF(a,b,DAY)+1
nb of days utilization = SUMX(SUMMARIZE(FILTER(ALLSELECTED('Table'),'Table'[Vessel]=MAX('Table'[Vessel])),'Table'[Project],"p",[days utilization]),[p])
Pbix attached.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |