Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm a DAX newbie and I'm working to a tight deadline. Despite a herculean effort to learn DAX (many hours) I find I am still woefully short of the skill level required to complete my task, so I am hoping someone can help me meet my deadline while I continue to learn.
SITUATION
I run a maintenance business and I am trying to forcaste my equipment availablilty into the future based on the number of vehicles in the fleet and when they are due into the maintenance area for servicing or inspection.
This graph shows the equipment demand vs availability by date.
With accompanying Table.
The drops in the EquipAv line (Light Blue) are dates that the equipment is due into the maint area for servicing or inspection. This is also shown as a decrease in numbers from "7" in the table.
The Raw data comes from the FactMaintSchedule table that holds the MaintDue, ERM (Registration Number), and other information on equipment type and maintenance type. I have removed the Registrations (ERM) for privacy reasons.
Vehicle Type | ERM | Maint Type | Maint Event | Maint Due |
LAND ROVER | XXXXXX | Inspection | FMT931 Comms (2)* | 08-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT931 Comms (2)* | 09-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT931 Comms (2)* | 09-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT934 Functional Check (1)* | 11-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT932 (W) 6 Monthly Safety Inspection (2) | 11-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT934 Functional Check (1)* | 12-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT934 Functional Check (1)* | 12-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT931 Comms (2)* | 15-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT932 (W) 6 Monthly Safety Inspection (2) | 15-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT934 Functional Check (1)* | 15-Nov-21 |
HGV | XXXXXX | Servicing | 6 Monthly (1) | 16-Nov-21 |
HGV | XXXXXX | Inspection | Tyre Life Check 2 Wheels | 16-Nov-21 |
HGV | XXXXXX | Inspection | FMT934 Functional Check (1)* | 16-Nov-21 |
HGV | XXXXXX | Inspection | FMT932 (W) 6 Monthly Safety Inspection (2) | 16-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT932 (W) 6 Monthly Safety Inspection (2) | 17-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT931 Comms (2)* | 18-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT934 Functional Check (1)* | 18-Nov-21 |
LAND ROVER | XXXXXX | Inspection | FMT932 (W) 6 Monthly Safety Inspection (2) | 18-Nov-21 |
LAND ROVER | XXXXXX | Servicing | OOPS 5 Yearly Drain/Flush And Replen Cooling System | 21-Nov-21 |
My data model is as follows:
DimEquipmentKey is a bridge table used to bridge a many to many relationship and is not really relevent here. I use it slice the data by equipment type. Both the graph and table above have been sliced by a specific equipment type. Once filtered by date it shows the equipment availability for that equipment, see the measure [EquipAv] below.
I am currently using the following simple DAX formulas.
[TotalEquipment] =COUNT(DimVehicleFleetMan[ERM]
[EquipAv] = [TotalEquipment] - DISTINCTCOUNT(FactMaintSched[ERM]
EquipDemand = SUM(FactTAFMISDemand[Required]) Not relevent to this problem
PROBLEM
When you see the drop in EquipAv line (light blue) this is the date that the equipment is due in for maintenance and is therefore not available for use based on the second measure [EquipAv].
The problem is that I know some of this equipment could be out of use for as much as 10 - 14 days, not just one day. It will probably come in the day before the Maint Due date and could leave again between 1 and 14 days after the Maint Due date, depending on equipment type.
OUTCOME
I would like to be able to do the following:
Unfortunately I cant share the data due to privacy / security reasons, but I'm happy to answer any questions and will share what I can if asked.
Thanks for any help in advance - good luck 🙂
Kind Regards
M
Hi @AS90Golds ,
--some of this equipment could be out of use for as much as 10 - 14 days, not just one day. It will probably come in the day before the Maint Due date and could leave again between 1 and 14 days after the Maint Due date, depending on equipment type.
Is there a table or column that record the equipment type and the corresponding out of use days?
Best Regards,
Jay
Hi Jay,
In desperation I thought I would add some more detail.
I have tried the following measures and variations on this theme but to no avail..
EuipAvSpread =
Var Period = DATESBETWEEN(
DimDate[Date],
DATEADD(
DimDate[Date], -3, DAY),
DATEADD(
DimDate[Date], 3, DAY))
VAR Result =
Calculate([TotalEquipment] - 1,
FILTER(
DimDate,
Period))
RETURN
Result
This just reduces the total equipment by 1 across all dates. Replacing the [TotalEquipment] measure with the [EquipAv] measure produces an error (A table of multiple values was supplied where a single value was expected).
I have also tried the following:
EquipAvSpread =
CALCULATE(
[TotalEquipment] -1,
DATEADD(DimDate[Date], -1 DAY),
DATEADD(DimDate[Date], -1 DAY),
DATEADD(DimDate[Date], -1 DAY)
)
Again this just removes 1 from all dates for both [TotalEquipment] and [EquipAv] measures. I'm not sure a CALCULATE function works as I don't think Iwant to change the filter context in the report. The calculation needs to depend on the date in the report.
I have tried many variations of the above but all do not work.
Maybe that will help a little.
Kind Regards
M
Hi Jay,
Thanks for your reply. Not at present but that would be very easy to produce. I could assign a calculated column to the DimVehicleFleet table that includes number of days for each different equipment type. The number of days in maintenance would be based on my own knowledge & experience, it is not data that I would take from a source.
Kind Regards
M
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
19 | |
17 | |
16 | |
15 |
User | Count |
---|---|
50 | |
24 | |
21 | |
17 | |
16 |