Selecting Dates in Shift based data file

[martijnth]

Dear Reader,

i'm stuck on the following. I have a data file with production time date (pieces), now i want to filter out the total amounts of the last shift. It is a 4 shift continous system someting like:

             1 jan19        2jan19     3jan19           4jan19

morning     A              A               C                   D

evening      B              B                D                   B   etc

now i need only the 'last' shift numbers, so figured, lets make a combination of date and shift (1jan19a etc) then try to filter last shift dates with something like: (this is year is collumn where is filter year=now())

This Year maxx Date = MAXX(FILTER(TBM_PRODUCTION_DATA,TBM_PRODUCTION_DATA[This Year]=1),TBM_PRODUCTION_DATA[Shift_date].[Date])

this works, but now i need to add a selection for shifts A,B,C,D, so tried to test for shift a only with:

test = IF (

related(MES_Profile[Shift]) = "A",

MAXX(FILTER(TBM_PRODUCTION_DATA,TBM_PRODUCTION_DATA[This Year]=1), TBM_PRODUCTION_DATA[Date + Shift NUM]), "Unreported ")

to see if it would give me last shift date for shift A. however it does not. below u see a screen dump of the table

I appreciate any help to get this one solved. THanks in advance.

reg martijn

[v-yuta-msft · Accepted solution]

@martijnth,

Based on your description, you may modify calculate column like pattern below:

test =
VAR result =
    CALCULATE (
        MAX ( TBM_PRODUCTION_DATA[Date + Shift NUM] ),
        FILTER (
            TBM_PRODUCTION_DATA,
            TBM_PRODUCTION_DATA[This Year] = 1
                && RELATED ( MES_Profile[Shift] ) = "A"
        )
    )
RETURN
    IF ( ISBLANK ( result ), result, "Unreported" )

Regards,

Jimmy Tao