Reduce total by 1 during a calculated time period.
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: Create a time period 1 day before and 'N' days after the maint due date depending on equipment. I have already looked at using an EOP / BOP Variable and DATEADD. This got me a time period but I dont really know what to do with it. During this time period I would like to reduce that equipment by 1 every day during the period. 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
