Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello, I am trying to follow instructions from this website, but w/o success.
I have personel attendance data splited by lunch break.
I would like to get Time of income (Min value) and time of outcome (max). Unfortunately our HR system works with minus values due to night shift.
Printscreen shows two rows with income -6:00 = 6pm and 00:30 = 00:30am next day.
In MIN collumn I would like to get -6:00, but I am still reaching 00:30.
Unique identificator is in red square (Personal Nr.+Date).
I am using this formula:
Solved! Go to Solution.
Hi @magicalmagic87 ,
Add a new column use the following expression:
DATE/TIME =
IF(
LEN( [Prichod] ) = 9,
TIMEVALUE( RIGHT( [Prichod], 8 ) )
+ DATE( [Year], [Month], [Day] - 1 )
+ TIME( 12, 0, 0 ),
TIMEVALUE( [Prichod] ) + DATE( [Year], [Month], [Day] )
// [Year], [Month], [Day] the same as your table [Rok] [Měsíc] [Den]
)
This column is a date/time type, so that you can compare time with this column. And if you just need the min time you can use the this dax. I did some change with your formula.
Min prichod =
VAR A =
CALCULATE(
MIN( 'manas Dochadzka2'[DATE/TIME] ),
FILTER(
'manas Dochadzka2',
'manas Dochadzka2'[identificator]
= EARLIER( 'manas Dochadzka2'[identificator] )
)
)
RETURN
CALCULATE(
MIN( 'manas Dochadzka2'[Prichod] ),
FILTER(
ALL( 'manas Dochadzka2' ),
'manas Dochadzka2'[identificator]
= EARLIER( 'manas Dochadzka2'[identificator] )
&& [DATE/TIME] = A
)
)
The result :
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @magicalmagic87 ,
Add a new column use the following expression:
DATE/TIME =
IF(
LEN( [Prichod] ) = 9,
TIMEVALUE( RIGHT( [Prichod], 8 ) )
+ DATE( [Year], [Month], [Day] - 1 )
+ TIME( 12, 0, 0 ),
TIMEVALUE( [Prichod] ) + DATE( [Year], [Month], [Day] )
// [Year], [Month], [Day] the same as your table [Rok] [Měsíc] [Den]
)
This column is a date/time type, so that you can compare time with this column. And if you just need the min time you can use the this dax. I did some change with your formula.
Min prichod =
VAR A =
CALCULATE(
MIN( 'manas Dochadzka2'[DATE/TIME] ),
FILTER(
'manas Dochadzka2',
'manas Dochadzka2'[identificator]
= EARLIER( 'manas Dochadzka2'[identificator] )
)
)
RETURN
CALCULATE(
MIN( 'manas Dochadzka2'[Prichod] ),
FILTER(
ALL( 'manas Dochadzka2' ),
'manas Dochadzka2'[identificator]
= EARLIER( 'manas Dochadzka2'[identificator] )
&& [DATE/TIME] = A
)
)
The result :
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @magicalmagic87 ,
I'd do the following.
Break out the "-" into a new column.
Convert the column into time data format.
Add a new data format conversion to decimal number.
Multiply that column by 24.
Multiply the column by -1 if there is a "-" in the first column.
If the column is negatve this should give you the number of hours you need to subtract from 12 to get the correct time.
Br,
J
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
101 | |
94 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |