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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
m_aa
Advocate I
Advocate I

Calculate a date when the last user got a record

Hi all,

 

I have a history of changes for appointments. I need to get a date which user was assigned last to the meeting and when. 

For example:

m_aa_1-1648630460574.png

The result I'm seeking:

UF_LAB_CODEUF_DATEUF_USER
P372819.09.202116
P373516.09.202116
P468502.11.202119

 

I managed to calculate the last user, but I can't get the date it was assigned to the last user (this is the earliest date when the last user has a record). I tried to get the minimum date when it was assigned to the user but in this case the origin and the last user are same person and I get the first date for the record.

 

Any thoughts on how to perform this?

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @m_aa ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column as below to get the date interval

Datediff = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[UF_DATE] ),
        FILTER (
            'Table',
            'Table'[UF_LAB_CODE] = EARLIER ( 'Table'[UF_LAB_CODE] )
                && 'Table'[UF_USER] = EARLIER ( 'Table'[UF_USER] )
                && 'Table'[UF_DATE] < EARLIER ( 'Table'[UF_DATE] )
        )
    )
RETURN
    DATEDIFF ( _predate, 'Table'[UF_DATE], DAY )

yingyinr_0-1649038926998.png

2. Create a measure as below to judge the date is on that date which the date interval is minimized

Datediff = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[UF_DATE] ),
        FILTER (
            'Table',
            'Table'[UF_LAB_CODE] = EARLIER ( 'Table'[UF_LAB_CODE] )
                && 'Table'[UF_USER] = EARLIER ( 'Table'[UF_USER] )
                && 'Table'[UF_DATE] < EARLIER ( 'Table'[UF_DATE] )
        )
    )
RETURN
    DATEDIFF ( _predate, 'Table'[UF_DATE], DAY )

3. Create a table visual and make a visual level filter with condition (Flag is 1)

yingyinr_1-1649039143424.png

Best Regards

Community Support Team _ Rena
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

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

Hi @m_aa ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column as below to get the date interval

Datediff = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[UF_DATE] ),
        FILTER (
            'Table',
            'Table'[UF_LAB_CODE] = EARLIER ( 'Table'[UF_LAB_CODE] )
                && 'Table'[UF_USER] = EARLIER ( 'Table'[UF_USER] )
                && 'Table'[UF_DATE] < EARLIER ( 'Table'[UF_DATE] )
        )
    )
RETURN
    DATEDIFF ( _predate, 'Table'[UF_DATE], DAY )

yingyinr_0-1649038926998.png

2. Create a measure as below to judge the date is on that date which the date interval is minimized

Datediff = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[UF_DATE] ),
        FILTER (
            'Table',
            'Table'[UF_LAB_CODE] = EARLIER ( 'Table'[UF_LAB_CODE] )
                && 'Table'[UF_USER] = EARLIER ( 'Table'[UF_USER] )
                && 'Table'[UF_DATE] < EARLIER ( 'Table'[UF_DATE] )
        )
    )
RETURN
    DATEDIFF ( _predate, 'Table'[UF_DATE], DAY )

3. Create a table visual and make a visual level filter with condition (Flag is 1)

yingyinr_1-1649039143424.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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