Why isn't isn't my Month to Date column working?

[k1s2]

Hi, I'm trying to get a column with a running total each month using this DAX in the column:

MTD Risks =
 CALCULATE(
    SUM(q_Compliance[Some Current Risk]),
    DATESMTD('Calendar'[Date]),
    USERELATIONSHIP(q_Compliance[Risk Assessment Date],'Calendar'[Date])
 )

...but I just get blanks in the colum:

What am I doing wrong?

[Dangar332 · Accepted solution]

hi, @k1s2 

i think below code is useful for your requirnment

MTD Risks = 
CALCULATE(
    SUM(q_Compliance[Some Current Risk]),
   DATESMTD(q_Compliance[Risk Assessment Date]),
   ALLEXCEPT('q_Compliance',q_Compliance[Risk Assessment Date])
)

[k1s2]

Thank you.  DO you have time to explain why it needs the ALLEXCEPT part? 

It seems counterintiutive. Surely I want it to include those dates?

[k1s2]

Power BI file is here: 

https://drive.google.com/file/d/1r6eNKsoxmrnzqjjIUiQG-XLHAcRO0IBk/view?usp=drive_link