Solved: Re: I need DAX for creating Exceptions(based on mo...

Anonymous · ‎07-02-2020

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

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.

v-zhenbw-msft · ‎07-24-2020

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.

View solution in original post

Anonymous · ‎08-17-2020

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

v-zhenbw-msft · ‎07-14-2020

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 · ‎07-20-2020

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

v-zhenbw-msft · ‎07-20-2020

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 · ‎07-22-2020

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.

v-zhenbw-msft · ‎07-23-2020

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 · ‎07-23-2020

Hello,

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

DU_ID	Account	Modified
MFG	TEREX	19-06-2020 23:12
BFS	CITIGROUP-CTO-RISK	09-06-2020 17:46
CMT	EY	28-06-2020 11:59
BFS	CITIGROUP-CTO-RISK	28-06-2020 14:54
CMT	TIME WARNER	19-07-2020 15:48
CMT	CAMPBELL FOODS	19-07-2020 23:35
CMT	P&G	19-07-2020 18:07
CMT	P&G	19-07-2020 18:02
MFG	CHEVRON	19-07-2020 10:54
MFG	CHEVRON	19-07-2020 03:59
MFG	UTC GROUP (Carrier)	17-07-2020 18:18
MFG	UTC GROUP (Otis)	19-07-2020 10:05
NR	SCANIA	19-07-2020 21:15
NR	NETS HOLDINGS	19-07-2020 11:18
NR	NETS HOLDINGS	19-07-2020 11:21
NR	NETS HOLDINGS	19-07-2020 11:27
NR	NETS HOLDINGS	19-07-2020 10:54
NR	NETS HOLDINGS	19-07-2020 11:09
NR	NETS HOLDINGS	19-07-2020 11:17
INS	BB&T Insurance Services	17-07-2020 20:17
INS	ONEMAIN SOLUTIONS	17-07-2020 10:27
INS	ONEMAIN SOLUTIONS	17-07-2020 10:28
EU	VEOLIA-WATERS	19-07-2020 22:50
EU	VEOLIA-WATERS	19-07-2020 22:54
BFS	CITIGROUP-ICG-MSST	19-07-2020 21:32
MFG	CHEVRON	19-07-2020 06:57
MFG	PBF ENERGY	17-07-2020 12:46
MFG	PBF ENERGY	17-07-2020 18:27
MFG	PBF ENERGY	17-07-2020 18:23
MFG	PBF ENERGY	17-07-2020 20:25
MFG	JCI	19-07-2020 15:54
CMT	INGRAM MICRO	17-07-2020 08:29
MFG	CHEVRON	17-07-2020 03:57
MFG	CHEVRON	15-07-2020 09:20
Europe	Lafarge	10-07-2020 20:07
INS	INSURITY	16-07-2020 19:49
CMT	VIACOM-CMT	15-07-2020 00:06
INS	INSURITY	19-07-2020 10:34
BFS	CITIGROUP-CTO-FINANCE	17-07-2020 16:23
BFS	CITIGROUP-CTI-CATE	19-07-2020 15:00
BFS	CITIGROUP-ICG-TTS/CPB	17-07-2020 18:48
BFS	CITIGROUP-CITI-GCB	19-07-2020 11:17
BFS	CITIGROUP-CTO-COMPLIANCE	19-07-2020 21:56
BFS	CITIGROUP-CTO-RISK	08-07-2020 08:13
BFS	CITIGROUP-CTO-RISK	15-07-2020 14:58
BFS	CITIGROUP-CTO-RISK	19-07-2020 19:54
NR	NETS HOLDINGS	19-07-2020 11:25
NR	NETS HOLDINGS	19-07-2020 12:21
NR	NETS HOLDINGS	19-07-2020 12:30
NR	NETS HOLDINGS	19-07-2020 12:36
NR	NETS HOLDINGS	19-07-2020 12:39
MFG	Kaman	19-07-2020 14:20

Thanks,

I.Ravi.,

v-zhenbw-msft · ‎07-24-2020

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.

v-zhenbw-msft · ‎07-28-2020

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 · ‎08-16-2020

For your DAX function not working fine.

Anonymous · ‎08-17-2020

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 · ‎07-02-2020

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.

amitchandak · ‎07-02-2020

@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

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly