Forum Discussion
Solved
Count rows with filtered dates
I am trying to create a measure with a count of people with graduation dates within a specific date range (June 2022 - Sep 2022) so they can be recognized at our event. In this table the graduation date is BTM_End_Date. There are no other tables involved. When I filter dates manually in the table view with datesbetween on or after 06/01/2022 and on or before 09/01/2022 I get 20 records.
I have tried the following:
BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), FILTER('pre-event', 'pre-event'[BTM_End_Date] >= 06/01/2022 & 'pre-event'[BTM_End_Date] < 09/01/2022 ))
- I get the result :
MdxScript(Model) (4, 94) Calculation error in measure 'pre-event'[BTM_within_90_days]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
If I remove the & 'pre-event'[BTM_End_Date] < 09/01/2022
- I get the result of 82 which is the total number of people with any graduation date.
If I try
BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', 'pre-event'[BTM_End_Date] <= 09/01/2022))
- I get a result of 283 which is the number of blank records. I checked this by running
BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', ISBLANK('pre-event'[BTM_End_Date])))
I have also tried :
BTM_within_90_days = COUNTAX('pre-event', DATESBETWEEN('pre-event'[BTM_End_Date], 06/01/2022, 09/01/2022))
Which returns (BLANK)
In the screenshot above, I show that the column I am using is formatted as a short date.
Any help is greatly appreciated.
I solved my own issue like this:
btm_within_90 = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', 'pre-event'[BTM_End_Date] > date(2022,06,01) && 'pre-event'[BTM_End_Date] < date(2022,09,01)))I had to specify my dates as such - date(2022,06,01)
1 Reply
I solved my own issue like this:
btm_within_90 = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', 'pre-event'[BTM_End_Date] > date(2022,06,01) && 'pre-event'[BTM_End_Date] < date(2022,09,01)))I had to specify my dates as such - date(2022,06,01)