Forum Discussion
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.
new one Exception =VAR _max_week =CALCULATE (MAX( 'query (16)'[week num] ),FILTER (ALL ('query (16)' ),'query (16)'[Month] = SELECTEDVALUE('query (16)'[Month])))var lastweekcount= CALCULATE([Total DU],FILTER('query (16)','query (16)'[week num]=_max_week))RETURN [Total DU]-lastweekcountThanks,
Community SupportHi 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.