Hello Team,
I need DAX for creating Exceptions(based on month selection filter {Total submissions on selected month- Last week submissions})
Currently, I'm using DAX like below mentioned:
NOTE: Calu Month = week num-1
After using this DAX(), associative property not working.
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.
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.
Hi @Anonymous ,
Here is our understanding:
The Total submissions calculate the total count DU_ID of each month.
The Exceptions calculate the total count of the previous week of the last week in each month.
For example, there are five weeks in January, then the Exceptions will calculate the total count in the fourth week.
If our understanding is correct, we can create two measure to meet your requirement.
1. First we need to create a week num column.
week num = WEEKNUM('query (16)'[Date],2)
2. Then we can create two measures to get the result.
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] )
)
)
VAR _last_week = _max_week - 1
RETURN
CALCULATE (
COUNT ( 'query (16)'[DU_ID] ),
FILTER ( ALLSELECTED ( 'query (16)' ), 'query (16)'[week num] = _last_week )
)
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
BTW, pbix as attached.
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
