DAX formula to check Activity of users in every quarter to determine RAG status

[Praj]

Hello Everyone,

I have the below input table which has the details about the User, Contract Start date of User, Activity done by User and Date of Activity. I am trying to find if users had atleast 2 activities every quarter starting from their contract start date. If they had atleast 2 activities per quarter, their status is green else its red. If an activity is scheduled in the future, their status is Amber

The output I am looking is below.

I am trying to create columns by adding 90 days to their contract start date and determining if they had atleast 2 activities in that period. However, I know this is not an efficient way as the days goes on, the number of column increses. 

If anyone has any efficient solution or approach it will be really helpful. I am also attaching the link to download the .pbix file for reference. 

https://drive.google.com/file/d/10OH0V_NiEnzzjJyLKXJFrE0jjlXblKrG/view?usp=share_link

Thanks in advance

Cheers,

Praj

[Anonymous · Accepted solution]

HI @Praj,

I'd like to suggest you extract the current user id and contract start date to use in the formula calculations.

You can user current user id as condition to filter corresponds records and use the year and quarter functions to extract year and quarter values from 'start date' to check on the 'activity date' field.

Tag =
IF (
    COUNTROWS (
        FILTER (
            Table,
            [User] = EARLIER ( Table[User] )
                && YEAR ( [Activity Date] ) = YEAR ( [Contract Start] )
                && QUARTER ( [Activity Date] ) = QUARTER ( [Contract Start] )
        )
    ) >= 2,
    "Y",
    "N"
)

Regards,

Xiaoxin Sheng