Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    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,
     

12 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.