Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
third_hicana
Helper III
Helper III

COUNT STATUS BY CURRENT MONTH

Hi DAX Experts. Seeking for your help with regards to the subject above.

 

Basically, I have two columns

Column 1 is the Date Created
Column 2 is the Status (i.e Open, Assigned and Withdrawn)

I wish to count the status for the current month only.

Thank you very much for your help 🙂

Sample Data:

Date CreatedStatus
05/10/2022Assigned
16/10/2022Open
20/10/2022Open
15/09/2022Withdrawn
1 ACCEPTED SOLUTION

Hi @third_hicana ,

 

You can try this method:

First of all, you need to change your date column to format "Date", not "text".

You can change it by this:

Right click the column and select Change Type and then Using locale.

vyinliwmsft_1-1667382170032.png

 

Create a table first:

 

 

Status = VALUES('Sample Table'[Status])

 

 

Then create a measure:

 

 

Count_per_status_of_current_month =
CALCULATE (
    COUNTROWS ( 'Sample Table' ),
    FILTER (
        'Sample Table',
        DATEDIFF ( [Date Created], TODAY (), MONTH ) = 1
            && [Status] = MAX ( 'Status'[Status] )
    )
)

 

 

 

vyinliwmsft_0-1667382141749.png

 

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

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

5 REPLIES 5
v-yinliw-msft
Community Support
Community Support

Hi @third_hicana ,

 

You can try this method:

Create a new table first:

Table = VALUES('Sample Table'[Status])

And then count the status:

New Column:

Count per status of current month = CALCULATE(COUNTROWS('Sample Table'),FILTER('Sample Table', MONTH('Sample Table'[Date Created]) = MONTH(TODAY()) && 'Sample Table'[Status] = 'Table'[Status]))

vyinliwmsft_0-1665044035851.png

 

 

Is that your expect result?

 

 

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

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

 

Hi @v-yinliw-msft The Date Created  was not able to detect while I'm writing the DAX. I made a relationship between the created Table and the Sample Data. But the month is unknown in DAX. Thanks

Hi @third_hicana ,

 

You can try this method:

First of all, you need to change your date column to format "Date", not "text".

You can change it by this:

Right click the column and select Change Type and then Using locale.

vyinliwmsft_1-1667382170032.png

 

Create a table first:

 

 

Status = VALUES('Sample Table'[Status])

 

 

Then create a measure:

 

 

Count_per_status_of_current_month =
CALCULATE (
    COUNTROWS ( 'Sample Table' ),
    FILTER (
        'Sample Table',
        DATEDIFF ( [Date Created], TODAY (), MONTH ) = 1
            && [Status] = MAX ( 'Status'[Status] )
    )
)

 

 

 

vyinliwmsft_0-1667382141749.png

 

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

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

 

amitchandak
Super User
Super User

@third_hicana , You can have measure like

 

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE(count(Table[Status]) FILTER('Table','Date'[Date Created] >=_min && 'Table'[Date Created] <= _max))

Hi @amitchandak  I forgot to mention. I want to count the per status of current month.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors