Reduce total by 1 during a calculated time period.

[AS90Golds]

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

[v-jayw-msft]

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

[AS90Golds]

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

[AS90Golds]

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