Year to Year Missing User ID's

[Raman3456]

Hello Everyone,

I have data like the below.

Date                 User ID              Formula Required

01-12-2021          12                    Need to find December to December Missed User IDs

02-12-2022           13

03-12-2023           14

07-12-2024           17

If I found a user ID in the next year December then user ID, if not Blank.

Please help me out with the DAX formula.

Regards,

Raman

[bhanu_gautam · Accepted solution]

@Raman3456 Ensure your data is in a table format, let's call it UserData.
Create a calculated column to extract the year from the date.
Create another calculated column to check if the user ID exists in the next year's December data.

NextYearUserID =
VAR CurrentYear = YEAR(UserData[Date])
VAR NextYear = CurrentYear + 1
VAR NextYearDecemberUserID =
CALCULATE(
MAX(UserData[User ID]),
FILTER(
UserData,
YEAR(UserData[Date]) = NextYear &&
MONTH(UserData[Date]) = 12 &&
UserData[User ID] = EARLIER(UserData[User ID])
)
)
RETURN
IF(ISBLANK(NextYearDecemberUserID), BLANK(), UserData[User ID])

[v-aatheeque]

Hi @Raman3456 ,

Have you resolved the issue? If yes, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!