Forum Discussion

third_hicana's avatar
third_hicana
Icon for Helper IV rankHelper IV
3 years ago
Solved

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) ...
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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.

     

    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] )
    )
)

     

     

     

     

    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.

     

third_hicana's avatar
third_hicana
Icon for Helper IV rankHelper IV
3 years ago

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

v-yinliw-msft's avatar
v-yinliw-msft
Icon for Community Support rankCommunity Support
3 years ago

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.

 

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] )
    )
)

 

 

 

 

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.