cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Count Specific Year and Month in a column

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.
 
 
Thanks
1 ACCEPTED SOLUTION
Microsoft
Microsoft

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:

v-xulin-mstf_0-1608270631694.png

 

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.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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:

v-xulin-mstf_0-1608270631694.png

 

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.

View solution in original post

@v-xulin-mstf 

Appreciated your help!. It's working perfectly 😀

 

Regards.

Super User IV
Super User IV

@Zaky , Create a measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors