Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The result I'm seeking:
UF_LAB_CODE | UF_DATE | UF_USER |
P3728 | 19.09.2021 | 16 |
P3735 | 16.09.2021 | 16 |
P4685 | 02.11.2021 | 19 |
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?
Solved! Go to Solution.
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 )
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)
Best Regards
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 )
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)
Best Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |