How to count number of month where sales is bigger than zero

[AdaL02]

hi,

I'm calculating for a contest. In order to be "qualified" for the contest, participants needs to have 12 months of sales in 2020.

my database generates data like this

Participants	date.year month	products	sales
A	1 Jan 2020	x	40
A	1 Feb 2020	x	20
A	1 Feb 2020	y	20
B	1 Jan 2020	x	50

so to achieve the purpose, I need to

1. sum the sales of participant per month (because each participant sells multiple products), then

2. Count number of months in 2020 whereby sales>0 for each particpant

3. if count number = 12, participant is qualified, if not not qualified

so this new column created will make the database look like this:

Participants	date.year month	products	sales	qualification
A	1 Jan 2020	x	40	qualified
A	1 Feb 2020	x	20	qualified
A	1 Feb 2020	y	20	qualified
B	1 Jan 2020	x	50	not qualified

Thanks to help!

AL

[v-yiruan-msft · Accepted solution]

Hi @AdaL02 ，

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column to get the yearmonth

YearMonth = CONCATENATE ( YEAR ( 'Table'[Date] ), FORMAT ( 'Table'[Date], "MM" ) )

2. Get the status which is qualified or not

   Method 1: First create a measure to get the sum of sales, then create a measure to get the status

Sum of sales = SUM('Table'[sales])

qualification = 
VAR _selyear =
    SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[YearMonth] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Participants] = SELECTEDVALUE ( 'Table'[Participants] )
                && YEAR ( 'Table'[Date] ) = _selyear
                && [Sum of sales] > 0
        )
    )
RETURN
    IF ( _count = 12, "qualified", "not qualified" )

   Method 2: Create a calculated column to get the status

Column = 
VAR _selyear =
    SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _sales =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            'Table',
            'Table'[Participants] = EARLIER ( 'Table'[Participants] )
                && YEAR ( 'Table'[Date] ) = _selyear
                && 'Table'[YearMonth] = EARLIER ( 'Table'[YearMonth] )
        )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[YearMonth] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Participants] = EARLIER ( 'Table'[Participants] )
                && YEAR ( 'Table'[Date] ) = _selyear
                && _sales > 0
        )
    )
RETURN
    IF ( _count = 12, "qualified", "not qualified" )

Best Regards

[AdaL02]

actually i hardcoded the _selyear = 2020, it works. of course, feel free to add solution on dynamic calculation but in the meanwhile, the DAX works! Thank you for the support! 

[AdaL02]

hi @v-yiruan-msft, 

Thanks for your explanation. I tried the solution but it shows everything "not qualified" in my actual database... So supposedly something is not right... 

Now in my actual database, I do have many years of data (perhaps 5 years of historical and 5 years of forecast). Qualification is to take place only if you have 12 months of sales (regardess what product(s) you are selling) in 2020.  Will there be an impact on _selyear variable in this case? By doing selectedvalue, all the years (as long as there is a value) will be selected. Hence whe it comes to return If part, no one has just 12 values to be qualified. Wonder how should I adjust the formula? It's ok to hardcode 2020. But also good to have dynamic coding. Qualification is always based on the current year - 1, 12 months of sales for every single month. 

Ada

[Mohammad_Refaei]

Create a measure like:

Qualified =
IF (
    CALCULATE (
        COUNTROWS ( Sales ),
        FILTER (
            ALLEXCEPT ( Sales, Sales[Participants] ),
            Sales[sales] > 0
                && YEAR ( Sales[Date] ) = YEAR ( Sales[Date] )
        )
    ) = 12,
    "Qualified",
    "Not Qualified"
)

Please check this sample file

[AdaL02]

hi Rafaei, 

Thanks for the explanation. I think the sample file was not right as May or APril of the month mentioned has 0 sales, yet the formula qualifies the participant. Also there are multiple products per each participant. Hence counting rows perhaps will lead to all participants not qualified....