Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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:

 

Exceptions =
Var MaxCount=MAX('query (16)'[Calu Month])bar.PNG
var b = SELECTEDVALUE('query (16)'[Month])
var c = CALCULATE(MAX('query (16)'[week num]),ALL('query (16)'))
return CALCULATE(COUNT('query (16)'[DU_ID]),All('query (16)'[Month]),'query (16)'[week num] <= MaxCount, 'query (16)'[Month]= b)

NOTE: Calu Month = week num-1

After using this DAX(), associative property not working.

 

2 ACCEPTED SOLUTIONS

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)

 

I1.jpg

 

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))

 

I2.jpg

 

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.

View solution in original post

Anonymous
Not applicable

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]-lastweekcount
 
Thanks,
 

View solution in original post

12 REPLIES 12
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

 

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.

Anonymous
Not applicable

Hello v-zhenbw-msft,

It's working current month only not for previous months. Please provide your valuable suggestions to me.

Thanks,

I.Ravi,

Cell: 8309454052

Hi @Anonymous ,

 

Sorry for that we can't reproduce your issue. Do you have a date table? Or do you just have a query(16) table?

 

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

This allows us or others to help you solve the issue.

 

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.

 

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.

Anonymous
Not applicable

Hello,

 

I'm using below mentioned DAX, it's somewhat working you can please help me to stabilize this one.

 

new one Exception =
VAR _max_week =
CALCULATE (
MAX( 'query (16)'[week num] ),
FILTER (
ALLSELECTED ( '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]-lastweekcount
 
Thanks,
I.Venkat.

Hi @Anonymous ,

 

Try this measure, hope it helps.

 

new one Exception =
VAR _max_week =
    CALCULATE (
        MAX ( 'query (16)'[week num] ),
        FILTER (
            ALLSELECTED ( 'query (16)' ),
            'query (16)'[Month] = SELECTEDVALUE ( 'query (16)'[Month] )
        )
    )
VAR _lastweek = _max_week - 1
VAR lastweekcount =
    CALCULATE (
        [Total DU],
        FILTER ( ALLSELECTED ( 'query (16)' ), 'query (16)'[week num] = _lastweek )
    )
RETURN
    [Total DU] - lastweekcount

 

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.

Anonymous
Not applicable

Hello,

 

Your mentioned DAX giving different behavior, please find attached sample excel data.

 

DU_IDAccountModified
MFGTEREX19-06-2020 23:12
BFSCITIGROUP-CTO-RISK09-06-2020 17:46
CMTEY28-06-2020 11:59
BFSCITIGROUP-CTO-RISK28-06-2020 14:54
CMTTIME WARNER19-07-2020 15:48
CMTCAMPBELL FOODS19-07-2020 23:35
CMTP&G19-07-2020 18:07
CMTP&G19-07-2020 18:02
MFGCHEVRON19-07-2020 10:54
MFGCHEVRON19-07-2020 03:59
MFGUTC GROUP (Carrier)17-07-2020 18:18
MFGUTC GROUP (Otis)19-07-2020 10:05
NRSCANIA19-07-2020 21:15
NRNETS HOLDINGS19-07-2020 11:18
NRNETS HOLDINGS19-07-2020 11:21
NRNETS HOLDINGS19-07-2020 11:27
NRNETS HOLDINGS19-07-2020 10:54
NRNETS HOLDINGS19-07-2020 11:09
NRNETS HOLDINGS19-07-2020 11:17
INSBB&T Insurance Services17-07-2020 20:17
INSONEMAIN SOLUTIONS17-07-2020 10:27
INSONEMAIN SOLUTIONS17-07-2020 10:28
EUVEOLIA-WATERS19-07-2020 22:50
EUVEOLIA-WATERS19-07-2020 22:54
BFSCITIGROUP-ICG-MSST19-07-2020 21:32
MFGCHEVRON19-07-2020 06:57
MFGPBF ENERGY17-07-2020 12:46
MFGPBF ENERGY17-07-2020 18:27
MFGPBF ENERGY17-07-2020 18:23
MFGPBF ENERGY17-07-2020 20:25
MFGJCI19-07-2020 15:54
CMTINGRAM MICRO17-07-2020 08:29
MFGCHEVRON17-07-2020 03:57
MFGCHEVRON15-07-2020 09:20
EuropeLafarge10-07-2020 20:07
INSINSURITY16-07-2020 19:49
CMTVIACOM-CMT15-07-2020 00:06
INSINSURITY19-07-2020 10:34
BFSCITIGROUP-CTO-FINANCE17-07-2020 16:23
BFSCITIGROUP-CTI-CATE19-07-2020 15:00
BFSCITIGROUP-ICG-TTS/CPB17-07-2020 18:48
BFSCITIGROUP-CITI-GCB19-07-2020 11:17
BFSCITIGROUP-CTO-COMPLIANCE19-07-2020 21:56
BFSCITIGROUP-CTO-RISK08-07-2020 08:13
BFSCITIGROUP-CTO-RISK15-07-2020 14:58
BFSCITIGROUP-CTO-RISK19-07-2020 19:54
NRNETS HOLDINGS19-07-2020 11:25
NRNETS HOLDINGS19-07-2020 12:21
NRNETS HOLDINGS19-07-2020 12:30
NRNETS HOLDINGS19-07-2020 12:36
NRNETS HOLDINGS19-07-2020 12:39
MFGKaman19-07-2020 14:20

 

Thanks,

I.Ravi.,

 

 

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)

 

I1.jpg

 

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))

 

I2.jpg

 

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 ,

 

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

 

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.

Anonymous
Not applicable

For your DAX function not working fine.

Anonymous
Not applicable

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]-lastweekcount
 
Thanks,
 
v-zhenbw-msft
Community Support
Community Support

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)

 

I1.jpg

 

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 )
    )

 

I2.jpg

 

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.

amitchandak
Super User
Super User

@Anonymous , for week Vs Week refer my blog

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

 

How to create a week calendar

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.