Count Specific Year and Month in a column

[Zaky]

Hi Experts,

I would like to count a specific year date and month in a column. I'm using IF condition or statement to do that.

Let says I want to count date data for 2020, 2019 and the blank data. In this case i create a column with conditions IF like below to get status for "Returned". It should count 2877

#RETURNED2019and2020andBLANK = CALCULATE(COUNTROWS('Data2020'), IF(YEAR('Data2020'[Batch Return])=2019 || YEAR('Data2020'[Batch Return])=2020 || YEAR('Data2020'[Batch Return])=BLANK(),1,0))

What if i want to count just a selected month for the year 2020? In this case i just want to count month of January and February only in 2020 without excluding year 2019 and Blank value?

Hope someone can help me figure it out.

Btw, pbix file attached here https://1drv.ms/u/s!AqKwe2kf8OBIdYE_lTSsBbWJCnc?e=ZPVoZf

Thanks

[v-xulin-mstf · Accepted solution]

Hi, @Zaky 

Depending on your needs, the following Measure is recommended：

Measure =

var a =

CALCULATE(

    COUNTROWS('Data2020'),

    IF(

        YEAR('Data2020'[Batch Return])=2019 ||  YEAR('Data2020'[Batch Return])=BLANK(),1,0)

    )

var b=

CALCULATE(

    COUNTROWS('Data2020'),    

    FILTER(

        Data2020,

        ((Data2020[Batch Return].[MonthNo])=1||(Data2020[Batch Return].[MonthNo])=2)&&YEAR(Data2020[Batch Return])=2020)

)

return

a+b

The result is as follows:

Here is the sample file that you can refer:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/ER7vE6w335pIj9KjTeD...

Best Regards,

Link Chen

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

[Zaky]

@v-xulin-mstf 

Appreciated your help!. It's working perfectly 😀

Regards.

[amitchandak]

@Zaky , Create a measure like

#RETURNED2019and2020andBLANK = CALCULATE(COUNTROWS('Data2020'), filter('Data2020', YEAR('Data2020'[Batch Return]) in {2019, 2020} || isblank('Data2020'[Batch Return])))

[Zaky]

Dear @amitchandak ,

What if i just want to count month of January and February only for 2020 without excluding year 2019 and Blank value?

For better understanding:

1) Count all in year 2019

2) Count only selected month (January and February) in year 2020

3) Count all for blank value.

Thanks again