Forum Discussion

third_hicana's avatar
third_hicana
Helper 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)

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
  • 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.

     

5 Replies

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    3 years ago

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

     

     

    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.

     

    • third_hicana's avatar
      third_hicana
      Helper 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
        Community 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.

         

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

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