Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

All() not filtering my data

I have created a dax table and wish to compare a mtd goal to actuals based on the number of working days in the month. Once in the table, the value for number of working days MTD changes based on the technician in the row.  I believe this is because the date for that row is filtered on the last order from that technician and not the current date, therefore the number of working days mtd is changing per technician.  The correct answer is 17, here is a sampling of the data in the table (never mind the $ signs) ...

 

My summary table code is: 

Test = SUMMARIZE(Orders,Users[DisplayName],'Order Created Date'[End of Month],"MTD Goal",[MTD Repair Goal Per Person],"Repair Sales MTD",[Repair Sales MTD])
 
 
The referenced measure, MTD Repair Goal Per Person is: 
calculate(count('Order Created Date'[Date]),all(users), datesmtd('Order Created Date'[Date]),'Order Created Date'[IsWorkday]=TRUE(),'Order Created Date'[Is Before Today]=TRUE())
 
I have tried all(users), all(orders), embedded calculate tables and nothing gives me the result of 17 down the line for all of the Oct 2019 rows.  Thank you in advance for your help!
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I solved the problem by creating a beginning of month and end of month variable and applying days between in the filter for the main variable.  Here are the three variables:

     

    var eom=if(month( (SELECTEDVALUE('Order Created Date'[End of Month])))=month(TODAY()) && year((SELECTEDVALUE('Order Created Date'[End of Month])))=year(TODAY()),TODAY(),SELECTEDVALUE('Order Created Date'[End of Month]))
     
    var bom=SELECTEDVALUE('Order Created Date'[Start of Month])
     
    var workdaysmtd = calculate(count('Order Created Date'[Date]),DATESBETWEEN('Order Created Date'[Date],bom,eom),'Order Created Date'[IsWorkday]=TRUE(),'Order Created Date'[Is Before Today]=true)

4 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi stephembree,

    The all function, will apply the result on all table instead of just on Oct month, you need to try to create a calculated column y&month=year([date filed])&month([date field])), then use allexcept(table,'table'[y&month] ) to replace all(table) to see whether it work or not.

    In addition, you also could inform me more detailed information (such  as your sample data or expected output or report file), then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    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

      Thank you for your reply.  I changed the variable to:

      var workdaysmtd = calculate(count('Order Created Date'[Date]),ALLEXCEPT('Order Created Date','Order Created Date'[Month and Year]), datesmtd('Order Created Date'[Date]),'Order Created Date'[IsWorkday]=TRUE(),'Order Created Date'[Is Before Today]=TRUE())
       
      This did not change the output. It seems that regardless of what I do with all() and allexcept() there is still filtering based on the orders that the user is associated with.  Why are orders being used to change the end date even though I am explicitly stating all(orders)?
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I solved the problem by creating a beginning of month and end of month variable and applying days between in the filter for the main variable.  Here are the three variables:

     

    var eom=if(month( (SELECTEDVALUE('Order Created Date'[End of Month])))=month(TODAY()) && year((SELECTEDVALUE('Order Created Date'[End of Month])))=year(TODAY()),TODAY(),SELECTEDVALUE('Order Created Date'[End of Month]))
     
    var bom=SELECTEDVALUE('Order Created Date'[Start of Month])
     
    var workdaysmtd = calculate(count('Order Created Date'[Date]),DATESBETWEEN('Order Created Date'[Date],bom,eom),'Order Created Date'[IsWorkday]=TRUE(),'Order Created Date'[Is Before Today]=true)
    • dax's avatar
      dax
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi stephembree, 

      It seems that you sloved this problem, right? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.