Need help with creating a key index

[Luukv93]

Hello,

So basically I have a FactSheet containing production data and I need to calculate the total shift time per production line.

Each productionline follows a D (Day) shift and a A (Night) shift. Since the staff occupation is highly flexible shift times are different for each production line.

Following the sample data below on 9 oct 2018 for production line 22 consider the day shift to start at 11:29 and end on 13:30.

Hence I used to create a KeyIndex:

DateShiftLijnIndex = 
CONCATENATE(FactProductie[ProductieDatum];FactProductie[Shift])&FactProductie[Lijn]

For now all is fine, however when the night shift works over midnight the production date will have 2 values (the startday and the next day). In order to solve this I have to calculate the min date for each DateShiftLijnIndex.

Below I created the measure that I believe will solve the issue, but I need somebody to complete it

MinDate = 
VAR MinDate = MIN(FactProductie[ProductieDatum])
RETURN
CALCULATE(MinDate; 
    FILTER(VALUES(FactProductie[DateShiftLijnIndex]

[Anonymous]

I am not sure if I understand this well enough, but why not use the start date in your key? But on the other hand you can get the min start date for your key with 

MinDate = 
VAR key = FactProductie[DateShiftLijnIndex]
RETURN
MINX(
  FILTER(
    FactProductie;
    FactProductie[DateShiftLijnIndex] = key
  );
  FactProductie[ProductieDatum]  // or the startdate
)

Regards,

Kristjan

[Luukv93]

Hi @Anonymous,

Thanks for rapid answer. Agree with your logic, only issue left is that DAX is unable to compare values of date with string. The error considers the use of VALUE or FORMAT. Any clue on how to apply this to the formula?

[Anonymous · Accepted solution]

Hi again,

1. You are missing the filter function around the table argument in the MINX function

2. You should not compair the date column to the variable, but the Index column to the variable.

This is how it should be:

VAR index = IndexColumn

RETURN

MINX(
  FILTER(
    Table,
    IndexColumn = index
  );
  DateColumn
)

Regards,

Kristjan

[Luukv93]

Fantastic it works thanks so much