Forum Discussion
Need your help on a calculated column
Hi,
I created a calculated column with the following formula
ShiftStart = VAR MinMaxDatumTijd = MIN(VirtualShift[MaxDatumTijd]) RETURN SWITCH( TRUE(); VirtualShift[Shift] = "D"; VirtualShift[MinDatumTijd]; VirtualShift[Shift] = "A"; CALCULATE( MinMaxDatumTijd; FILTER( SUMMARIZE(VirtualShift; VirtualShift[Shiftdatum]; VirtualShift[Shift]); VirtualShift[Shift] = "A")))
When shift is "D" (Day) it must take the MinDatumTijd of the current row. This works fine so far.
When shift is "A" (Evening) it must take the MaxDatumTijd of the "D" shift. This only works for the first row, for each other row it takes the outcome of the first row. I believe it should differently but can't come up with the right solution.
Would really appreciate your help
Sample data:
Shiftdatum Shift MinDatumTijd MaxDatumTijd ShiftdatumShift ShiftStart
2-1-2019 0:00:00 A 2-1-2019 15:00:00 2-1-2019 23:45:51 2-1-2019A 2-1-2019 17:00:00
2-1-2019 0:00:00 D 2-1-2019 6:00:00 2-1-2019 17:00:00 2-1-2019D 2-1-2019 6:00:00
3-1-2019 0:00:00 A 3-1-2019 15:00:00 3-1-2019 23:59:00 3-1-2019A 2-1-2019 17:00:00
3-1-2019 0:00:00 D 3-1-2019 6:00:00 3-1-2019 16:30:00 3-1-2019D 3-1-2019 6:00:00
4-1-2019 0:00:00 A 4-1-2019 15:00:01 5-1-2019 2:00:52 4-1-2019A 2-1-2019 17:00:00
4-1-2019 0:00:00 D 4-1-2019 6:00:00 4-1-2019 17:00:00 4-1-2019D 4-1-2019 6:00:00
5-1-2019 0:00:00 D 5-1-2019 6:00:00 5-1-2019 12:00:51 5-1-2019D 5-1-2019 6:00:00
6-1-2019 0:00:00 A 6-1-2019 17:00:00 6-1-2019 23:30:03 6-1-2019A 2-1-2019 17:00:00
7-1-2019 0:00:00 A 7-1-2019 15:00:00 8-1-2019 0:00:13 7-1-2019A 2-1-2019 17:00:00
7-1-2019 0:00:00 D 7-1-2019 6:00:00 7-1-2019 14:30:31 7-1-2019D 7-1-2019 6:00:00
Hi Luukvv93
Create a column
Column = SWITCH ( TRUE (), Sheet5[Shift] = "D", Sheet5[MinDatumTijd], Sheet5[Shift] = "A", CALCULATE ( MIN ( Sheet5[MaxDatumTijd] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Shiftdatum] ), Sheet5[Shift] = "D" ) ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Luukvv93
Create a column
Column = SWITCH ( TRUE (), Sheet5[Shift] = "D", Sheet5[MinDatumTijd], Sheet5[Shift] = "A", CALCULATE ( MIN ( Sheet5[MaxDatumTijd] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Shiftdatum] ), Sheet5[Shift] = "D" ) ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hmm, copying and pasting that data didn't work very well, it all came out in a single column. But, what you want is EARLIER to grab the D row. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Pretty sure you are doing something fairly similar to that article in essense.
With your instructions I tried the following, unfortunately without any result.
Could someone explain me what I'm doing wrong?
ShiftStart = VAR MaxDatumTijd = MAX(VirtualShift[MaxDatumTijd]) RETURN SWITCH( TRUE(); VirtualShift[Shift] = "D"; VirtualShift[MinDatumTijd]; VirtualShift[Shift] = "A"; MAXX( FILTER( VirtualShift; VirtualShift[Shift] = EARLIER(VirtualShift[Shift]) && VirtualShift[Shift] = "D"); VirtualShift[MaxDatumTijd]) )
Any clue anyone?
