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:   ...
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,
     
v-zhenbw-msft's avatar
v-zhenbw-msft
Community 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
    Community 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

        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.