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...
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)
dax's avatar
dax
Community 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)?