DAX not giving the right results with SUMX

[deepak_d03]

I have the car crash information for the last few years (2015 -2023). I am trying to get the count of car crashes against each year - month combination. I created a Calendar table and connected it with my master data table. (Calendar date and Crash date). I have the below DAX formula for the crash count. 

Crash Count =

    var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE])

    VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE])

    RETURN

    CALCULATE(SUMX(Traffic_Crashes,

                    IF(Traffic_Crashes[CRASH_DATE] >= FirstDayOfMonth &&

                        Traffic_Crashes[CRASH_DATE] <= LastDayOfMonth, 1)))

But the metrix that I have does not give the expected result. Any thoughts? 

[deepak_d03]

@tamerj1  - can you check on this one? 

DAX for Calendar table - 

Calendar = ADDCOLUMNS(

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "mmmm"),

    "Day of week", FORMAT([Date], "dddd"),

    "Month Number", MONTH([Date])

)

Date and Crash_Date are connected.

The data type is the same for both which is date.

[tamerj1]

Hi @deepak_d03 

How about 

Crash Count = COUNTROWS ( Traffic_Crashes )

[deepak_d03]

The result is the same. Not getting the correct one.

CRASH COUNT NEW is the one with countrows(Crash_Date).

Has it to do anything with the way the Calendar table is created and it connected to the master table? I have posted the DAX formula for Calendar table and the data model. Posting here a screenshot of -the crash data table and Calendar table too.  CC - @Greg_Deckler =- Please see the screenshots for the sample data and the expected results. 

[Greg_Deckler]

@deepak_d03 That's a weird way of doing that,try either:

Crash Count =
    var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE])
    VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE])
    RETURN
    CALCULATE(
      COUNTROWS('Traffic_Crashes'),
      Traffic_Crashes[CRASH_DATE] >= FirstDayOfMonth &&
      Traffic_Crashes[CRASH_DATE] <= LastDayOfMonth 
    )

or

Crash Count =
    var FirstDayOfMonth = MIN(Traffic_Crashes[CRASH_DATE])
    VAR LastDayOfMonth = MAX(Traffic_Crashes[CRASH_DATE])
    VAR __Table = FILTER( 'Traffic_Crashes', [CRASHDATE] >= FirstDayOfMonth && [CRASH_DATE] <= LastDayOfMonth )
    VAR __Result = COUNTROWS( __Table )
RETURN
  __Result

[deepak_d03]

@Greg_Deckler  - getting the same result. 

[Greg_Deckler]

@deepak_d03 Going to need to share some sample data then and expected results. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.