Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to get MIN date value for selected ID

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:

Prichod_MIN = CALCULATE(MIN('manas Dochadzka2'[Prichod]),FILTER(ALL('manas Dochadzka2'),'manas Dochadzka2'[Identifikator_dochazka]= EARLIER('manas Dochadzka2'[Identifikator_dochazka])))
 
Many thanks for any help!
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    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.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    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.

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous , 

    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