How to Use AllExcept to Calculate Backlog & Allow for a Filter?

[Dave1mo1]

Hi,

I have the DAX below to calculate unit backlog for my company. However, I want to be able to filter it based on a "Business Unit" value. How can I preserve the syntax that retains the date filters appropriately to calculate backlog while still allowing me to filter? I tried "ALLEXCEPT," but can't get the syntax to work correctly.

If necessary, "Backlog" is defined as an order that's been booked but not yet invoiced as of the selected date.

Thank you!!!

Total Backlog =

CALCULATE(

    SUM(
'Order Data'[Units]),

FILTER(

    ALL(

        'Order Data'),

        'Order Data'[Booked Date] <MIN ('FY Date Table'[Date])

&&

OR('Order Data'[Invoice Date] >= MIN ('FY Date Table'[Date]), ISBLANK('Order Data'[Invoice Date]

)))

)

[Ray_Minds]

Hi @Dave1mo1 
1.Create measure All Except = Calculate(sum(Data [sales]),ALLEXCEPT('Data' ,Data[sales],Data [Country]))

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Anonymous]

Hi @Dave1mo1 

Thank you for ahadkarimi reply and solution.

In addition to the use of “ALLEXCEPT” it is also possible to use “KEEPFILTER”, please allow me to add below.

_Total Backlog = 
CALCULATE(
    SUM('Order Data'[Units]),
    FILTER(
        'Order Data',
        'Order Data'[Booked Date] < MIN('FY Date Table'[Date])
        &&
        OR(
            'Order Data'[Invoice Date] >= MIN('FY Date Table'[Date]),
            ISBLANK('Order Data'[Invoice Date])
        )
    ),
    KEEPFILTERS('Order Data'[Business Unit])
)

Here is my test result, see uploaded pbix file for details.

^{Best Regards,}

^{Jarvis Tang}

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

[Dave1mo1]

Hi, and thanks for your response!

I am getting the following error:

MdxScript(Model) (289, 17) Calculation error in measure 'Measure Table' [Total Backlog]: Cannot convert value 'West' of type Text to type True/False.

Do I need to convert these business unit names to numerical values?

[Anonymous · Accepted solution]

Hi @Dave1mo1 

As you understand, using “KEEPFILTER” in this measure requires changing your “Business Unit” data type to numeric type. If you prefer to use a text type, using “ALLEXCEPT” is more appropriate.

The DAX using “ALLEXCEPT” is as follows:

__Total Backlog = 
CALCULATE(
    SUM('Order Data'[Units]),
    FILTER(
        ALLEXCEPT('Order Data', 'Order Data'[Business Unit]),
        'Order Data'[Booked Date] < MIN('FY Date Table'[Date])
        &&
        OR(
            'Order Data'[Invoice Date] >= MIN('FY Date Table'[Date]),
            ISBLANK('Order Data'[Invoice Date])
        )
    )
)

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

[Dave1mo1]

This did it - thanks so much!

[ahadkarimi]

Hi @Dave1mo1, give this a try, and if you encounter any issues, let me know.

Total Backlog =
CALCULATE(
    SUM('Order Data'[Units]),
    FILTER(
        ALLEXCEPT('Order Data', 'Order Data'[Business Unit], 'Order Data'[Booked Date], 'Order Data'[Invoice Date]),
        'Order Data'[Booked Date] < MIN('FY Date Table'[Date]) &&
        OR('Order Data'[Invoice Date] >= MIN('FY Date Table'[Date]), ISBLANK('Order Data'[Invoice Date]))
    )
)

Did I answer your question?  If so, please mark my post as the solution!
Your Kudos are much appreciated!  Proud to be a Resolver III !

[Dave1mo1]

Hi, 

Thank you for your response! I'm getting the following error:

"A single value for column 'Booked Date' in table 'Order Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggreggation such as min, max, count, or sum to get a single result."

Any thoughts?

[ahadkarimi]

Hi @Dave1mo1, give this a try, and if you encounter any issues, let me know.

_Total Backlog =
CALCULATE(
    SUM('Order Data'[Units]),
    FILTER(
        ALLEXCEPT('Order Data', 'Order Data'[Business Unit]),
        MIN('Order Data'[Booked Date]) < MIN('FY Date Table'[Date]) &&
        OR(MAX('Order Data'[Invoice Date]) >= MIN('FY Date Table'[Date]), ISBLANK(MAX('Order Data'[Invoice Date])))
    )
)

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!