DAX: filter for a user and a particular year and find his consistency in sales

[Ishaq]

Hi, 
I have a sales table which contains username and Dateof sale column along with few products column as shown below.

The dateofsale column is truncated to the begining of the month and each user will have only 1 entry for each month as shown below

I am trying to find whether each user is consistent is his sales in the Year 2018, 2019 and 2020, meaning if the user has done sales in each month of the year, he is consistent, else he is not. In the above pic, user 700081, has done sales for each month in the Year 2018 (total month count 12) , hence he is consistent in 2018, but in the year 2019 he has done sales only for 3 months in 2019 and hence he is not consistent in the year 2019, the same user has no sales in 2020, hence he is inconsistent in the Year 2020 as well.

I tried to achieve this is 3 steps.

Step 1 : If a user has done sales in the year 2018, then give the output as 1, else 0. I created a calculated column as shown below

Year2018 = IF(

                CALCULATE(

                    COUNTROWS(Sales),

                    Sales[DateofSale].[Year] = 2018,

                    REMOVEFILTERS(Sales[Year2019]),

                    REMOVEFILTERS(Sales[Year2020]),

                    ) > 0

                ,1,0)

Pls ignore the removefilters line as when i try to copy the same formula for 2019 and 2020. I am getting circular dependency error and that is why I included removefilters function.
This dax gives me 1 if there is sales done in the year 2018 for each record, else 0. 

Step 2: After creating the above dax column , I created a measure

Year2018_Count = CALCULATE(

    COUNT('Sales'[Year2018]),

    ALLEXCEPT('Sales', 'Sales'[UserName])

)

This should give me the count for each user for a particular year in this case 2018. 

Step 3: Then I created a dax column again

Status_2018 = IF(Sales[Year2018_Count]<12,"Inconsistent","Consistent")

How this is getting failed for me. as for the user 700081, i am getting consistent for 2018, 2019, 2020 , which is incorrect.

I hope I have explained this in detail, If you guys need any clarification , pls do tell.
If you guys have an alternative /  much simpler way to achieve my goal, pls do share it as well.

[Anonymous · Accepted solution]

Hi, @Ishaq 

You can try the following dax to achieve your need.

Dax:

Year = YEAR([DateofSale]) 

YearMonth = YEAR([DateofSale]) & "-" & MONTH([DateofSale])

MonthCount = 
CALCULATE(
    DISTINCTCOUNT(Sales[YearMonth]),
    ALLEXCEPT(Sales, Sales[UserName], Sales[Year])
)


ConsistencyCheck = 
IF(
    MAX(Sales[MonthCount]) = 12,
    "Inconsistent",
    "Consistent"
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum