Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
magicalmagic87
Frequent Visitor

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.

magicalmagic87_1-1637329187710.png

 

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!
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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 :

vchenwuzmsft_0-1637648597680.png

 

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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 :

vchenwuzmsft_0-1637648597680.png

 

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
Community Champion
Community Champion

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


Connect on LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.