Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago
Solved

Count Rows for this month and between a specific range

Hi Experts

I have a summarised table which I am trying to count the number of occurences for this month between a specific range of values for Active customers only

 

Here is my example data

 

CustID    Status    Date             Number of orders 

001         Active        1 jan                  200

002         Closed       1 Jan                 50

003         Active        3 Jan                 250

004         Active        4 Jan                350

005         Active        5 Jan                1000

 

I am looking to count customer rows for January (or current month) which are active and between the range of over 200 and less than 350 orders

 

here is my dax but it is bringing blank rather than 3

 

Orders between 200-350 this month = CALCULATE(

        COUNTROWS('Customer Summary Table'),
        FILTER('Customer Summary Table', MONTH('Customer Summary Table'[date])= MONTH(TODAY()) &&  YEAR('Customer Summary Table'[Date]) = YEAR(TODAY()) && 'Customer Summary Table'[Status] = "Active" && 'Customer Summary Table'[Number of Orders] > 200 && 'Customer Summary Table'[Number of Orders] < 350
))

Thanks in advance

Need Help
  • vicky_'s avatar
    vicky_
    1 year ago

    Your measure is working fine for me - i just made some adjustment based on your sample data to only look at Jan 2024

    BTW - if your expected results are 3 for the sample data above, you'll need to use >= and <=

    I'm not sure what your actual data / report looks like, but you can check if you've got any other filters causing the result to be blank on your report by going to the top-right of the visual and checking there. 

     

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    1 year ago

    Your measure is working fine for me - i just made some adjustment based on your sample data to only look at Jan 2024

    BTW - if your expected results are 3 for the sample data above, you'll need to use >= and <=

    I'm not sure what your actual data / report looks like, but you can check if you've got any other filters causing the result to be blank on your report by going to the top-right of the visual and checking there.