Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Solved! Go to 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
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
Hi @Kristjan76,
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?
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
Fantastic it works thanks so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |