topic How to Use AllExcept to Calculate Backlog & Allow for a Filter? in DAX Commands and Tips

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

Dave1mo1 — Mon, 19 Aug 2024 17:39:42 GMT

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]

)))

)

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

ahadkarimi — Mon, 19 Aug 2024 19:51:19 GMT

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 !

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

Anonymous — Tue, 20 Aug 2024 05:39:40 GMT

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.}

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

Dave1mo1 — Tue, 20 Aug 2024 18:49:07 GMT

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?

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

Dave1mo1 — Tue, 20 Aug 2024 18:55:16 GMT

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?

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

Ray_Minds — Wed, 21 Aug 2024 12:59:53 GMT

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.

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

Anonymous — Fri, 23 Aug 2024 07:10:57 GMT

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.

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

ahadkarimi — Fri, 23 Aug 2024 14:19:35 GMT

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!

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

Dave1mo1 — Fri, 23 Aug 2024 17:34:25 GMT

This did it - thanks so much!