Units join and leave the fleet need to be exclude from calculation

[Anonymous]

Hi Guys, need some help and thank you in advance.

I have to report number of maintenace tasks open accross the fleet (Trucks), those events are calculated per month and I do not have any issue.

Formula:

# Due Jobs (Archive) = CALCULATE(
    DISTINCTCOUNT(MAINTENANCE_JOBS_ARCHIVE[Job ID]),
    GENERATE(
        VALUES('T_DIM_DATE (Jobs)'[Date]),
        FILTER(FILTER(MAINTENANCE_JOBS_ARCHIVE, MAINTENANCE_JOBS_ARCHIVE[Maintenance Type]="Scheduled Maintenance"),
            CONTAINS(
                DATESBETWEEN('T_DIM_DATE (Jobs)'[Date],
                             STARTOFMONTH(MAINTENANCE_JOBS_ARCHIVE[Due Date]),
                             ENDOFMONTH(MAINTENANCE_JOBS_ARCHIVE[Done Date])
                             ),
                             [Date], 'T_BASS_DIM_DATE (Jobs)'[Date])
        )
    ))
//Events-In-Progress per Month (where the Due date is transform as first day in the month and Done Date Last day of the Month).

The issue is with Truck Join Date to the fleet and the Left date:

Example:

One unit join 01/04/2014 and still on the fleet,

Second Unit Join 01/01/2000 and left 1/12/2014.

I need to exclude the tasks from first unit from the calculation before she join, and exclude the open tasks aftern the second unit left. And still be able to see my month open tasks open per month as they are manager.

3 tables related T_DIM_DATE; MAINTENANCE_JOBS_ARCHIVE; UNIT_DETAILS

[Anonymous · Accepted solution]

Hi Guys, @v-chuncz-msft,

Did rest on the subject and did also solve as @v-chuncz-msft suggested creating a Calculating table, that solution did increase my file size (I'm almost in the limite.

But did solve and here is the solution:

Did create two measures the first to list all jobs during asset life time:

Truker life Jobs =
VAR Managed = FILTER(TRUCK_DETAILS,TRUCK_DETAILS[Management Type Code]="F")
Return
CALCULATE(DISTINCTCOUNT(MAINTENANCE_JOBS_ARCHIVE[Job ID]),
    GENERATE(
        VALUES('DIM_DATE (Jobs)'[Date]),FILTER(Managed,
            CONTAINS(DATESBETWEEN('DIM_DATE (Jobs)'[Date],
                TRUCK_DETAILS[Takeover Date], TRUCK_DETAILS[LeftDate]),[Date], 'DIM_DATE (Jobs)'[Date])))
)// This Measure create the outside contanor, bringing all Jobs open for the period TakeOver untill leftdate.

Then did create a second measure looking to all jobs open in the first container:

# test1 =
VAR ScheduledJobs = FILTER(MAINTENANCE_JOBS_ARCHIVE,
    MAINTENANCE_JOBS_ARCHIVE[Maintenance Type]="Scheduled Maintenance")
Return

 CALCULATE(
    [Truker life Jobs],
    GENERATE(
        VALUES('DIM_DATE (Jobs)'[Date]),
        FILTER(ScheduledJobs,
            CONTAINS(
                DATESBETWEEN('DIM_DATE (Jobs)'[Date],
                             STARTOFMONTH(MAINTENANCE_JOBS_ARCHIVE[Due Date]),
                             ENDOFMONTH(MAINTENANCE_JOBS_ARCHIVE[Done Date])
                             ),
                             [Date], 'DIM_DATE (Jobs)'[Date])
        )
    ))
//Events-In-Progress per Month (where the Due date is transform as first day in the month and Done Date Last day of the Month).

Hope to assist someone else with same issue.

Regards

@Anonymous

[v-chuncz-msft]

@Anonymous,

To make things easy, you may try to add calculated tables.