Calculated Column from another column - change all but the latest date.

[Anonymous]

i have three columns - status, user and month.   the status is the same for every month for each user but it is only correct for the last month, all earlier entries should be "Active".

I had a solution in power query using a function that works on my home latest release power BI version, howerver my work power BI desktop is an older version due to IT policy and i get a function error.   

is there a way in DAX to produce the column i need?

how it is:

Status	User	Month
Complete	User1	1/01/2021
Complete	User1	1/02/2021
Complete	User1	1/03/2021
Withdrawn	User2	1/01/2021
Withdrawn	User2	1/02/2021
Withdrawn	User2	1/03/2021
Active	User3	1/01/2021
Active	User3	1/02/2021
Active	User3	1/03/2021

how it should be:

Status	User	Month
Active	user1	1/01/2021
Active	User1	1/02/2021
Complete	user1	1/03/2021
Active	User2	1/01/2021
Active	User2	1/02/2021
Withdrawn	User2	1/03/2021
Active	User3	1/01/2021
Active	User3	1/02/2021
Active	User3	1/03/2021

[SanketBhagwat]

Can you please share the sample data file?

The data you provided seems to be incomplete as User2 has different status for same day.

Regards,

Sanket Bhagwat

[Anonymous]

appoligies - i have changed the data in the post.  should have been 3 users. 

[ERD]

@Anonymous ,

you have 2 different values for the last month for User 2..

[Anonymous]

i've updated the post - it hsould have been 3 users. 

[ERD · Accepted solution]

@Anonymous ,

You can try this measure:

Measure = 
VAR currentUser = MAX ( T[User] )
VAR latestDate = MAXX ( FILTER ( ALL ( T ), T[User] = currentUser ), T[Month] )
VAR latestStatus =
    CALCULATE (
        MAX ( T[Status] ),
        T[User] = currentUser,
        T[Month] = latestDate
    )
RETURN
    IF ( MAX ( T[Month] ) = latestDate, latestStatus, "Active" )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

Hi ERD,  thanks for the reply - i'm not sure if i am doing something wrong but when i'm doing it i get "Active" for all results

[ERD]

@Anonymous , the code is for the measure, your image shows a calculated column. You need a calculated column ? If so, here the code:

New status = 
VAR currentUser = T[User]
VAR latestDate = 
    MAXX ( FILTER ( ALL ( T[User], T[Month] ), T[User] = currentUser ), T[Month] )
VAR latestStatus =
    CALCULATE (
        MAX ( T[Status] ),
        T[User] = currentUser,
        T[Month] = latestDate
    )
RETURN 
    IF ( T[Month] = latestDate, latestStatus, "Active" )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

ERD thankyou for this - i was also able to get the first response to work once i corrected my mistake so i'll accept that as the solution as it answers the original question.