I need DAX for creating Exceptions(based on month selection {Total submissions- Last week submissio)

Hi Anonymous ,

We can use the following steps to meet your requirement.

1. We need to create three calculate columns to get month, month number and week num.

Month = FORMAT('query(16)'[Modified],"mmm")

Month number = MONTH('query(16)'[Modified])

week num = WEEKNUM('query(16)'[Modified],2)

2. Then we can create the following measures to get the Total and Exceptions.

Total submissions = 
CALCULATE(COUNT('query(16)'[DU_ID]),FILTER(ALLSELECTED('query(16)'),'query(16)'[Month]=MAX('query(16)'[Month])))

Exceptions = 
var _max_week = CALCULATE(MAX('query(16)'[week num]),FILTER(ALLSELECTED('query(16)'),'query(16)'[Month]=MAX('query(16)'[Month])))
return
CALCULATE(COUNT('query(16)'[DU_ID]),FILTER(ALLSELECTED('query(16)'),'query(16)'[week num]=_max_week))

new one Exception = 
var _max_week = CALCULATE(MAX('query(16)'[week num]),FILTER(ALLSELECTED('query(16)'),'query(16)'[Month]=MAX('query(16)'[Month])))
return
[Total submissions] - CALCULATE(COUNT('query(16)'[DU_ID]),FILTER(ALLSELECTED('query(16)'),'query(16)'[week num]=_max_week))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

I'm used below mentioed DAX, after working fine my Application.