Using Complex Logic Inside Iterating Functions - Advanced DAX


#1

#2

This technique looks incredibly useful. I’m trying to use it for a specific requirement in my dataset. I have a monthly metric called ‘Late backlog’ that sums the order value of all shipments that are open and past due at the end of the month; see example below:

The piece I’m struggling with is to get the Order that is late over a number of months to show up multiple times … i.e. for each month that it is open and past due. My interpretation of the complex logic inside an iterating function is below but it errors out with “multiple values supplied where a single value was expected”. Any help appreciated!

Late backlog (v2, iterating) =
SUMX (
    FILTER (
        'Sales table',
        DATESBETWEEN (
            DATE_MAS[DATE_KEY],
            SAMEPERIODLASTYEAR ( ENDOFMONTH ( LASTDATE ( DATE_MAS[DATE_KEY] ) ) ),
            ENDOFMONTH ( LASTDATE ( DATE_MAS[DATE_KEY] ) )
        )
    ),
    SWITCH (
        TRUE (),
        MONTH ( 'Sales table'[Ship Date] ) <> MONTH ( 'Sales table'[Due date] )
            && 'Sales table'[Days Late] > 0, [Total Sales $],
        0
    )
)

#3

Just so I can comprehend this correctly,

Is you context of the calculation every month?

So for every month you want to know what was and is considered backlog? The total amount of the a breakdown of every single order?

I’m going to have to try to mock this up as there’s quite a bit going on here that would need to be tested I think


#4

I’ve mocked this up and I think you need to thing about this a little differently.

Here’s the results

This idea centers around a techniques called events in progress

Here’s is where this is covered at Enterprise DNA Online

The important thing to note here is the model

You see two inactive relationships

Then the formula the utilise

Backlog Orders = 
CALCULATE( SUM( 'Order'[Value] ),
	FILTER( ALL( 'Order'[Due Date] ), 'Order'[Due Date] < MIN( Dates[Date] ) ),
	FILTER( ALL( 'Order'[Ship Date] ), 'Order'[Ship Date] > MIN( Dates[Date] )))

It’s a lot simpler than you current formula I think. It’s just the model you need to have a think about.

I believe these are calculating the correct results, let me know if not

Thanks


#5

Here’s the model attached to review.

Backlog Orders.pbix (93.4 KB)


#6

Wow - thank you for this. As you say, your approach is much simpler.

We haven’t quite got to the results I’m after yet though, and sorry that I didn’t quite explain what I’m trying to achieve clearly enough.

To answer your questions:

  • yes, the context of the calculation is each month
  • so for every month, I want to know what orders remained open at the end of the month AND are late to the due date

In all my current efforts I can only manage to show the open&late orders in one month, but Order 1 in our dataset should show up in 3 months (March, April, May). I had assumed that the trick was going to be to push the Open/Late calculation into an iterating function, but I’m sure you can see a much simpler solution!

I’ve attached a slightly modified and annotated version of your mock-up to try and help clarify things.

Backlog Orders v1.pbix (100.6 KB)


#7

Is this correct?

Seems to be if I look through the table

Just made small adjustment here

Backlog Orders = 
CALCULATE( SUM( 'Order'[Value] ),
	FILTER( VALUES( 'Order'[Due Date] ), 'Order'[Due Date] <= MAX( Dates[Date] ) ),
	FILTER( VALUES( 'Order'[Ship Date] ), 'Order'[Ship Date] >= MAX( Dates[Date] )))

Attached

Backlog Orders v1 (1).pbix (101.4 KB)


#8

Yes Yes Yes! … with one small tweak:

Backlog Orders =
CALCULATE( SUM( ‘Order’[Value] ),
FILTER( VALUES( ‘Order’[Due Date] ), ‘Order’[Due Date] <= MAX( Dates[Date] ) ),
FILTER( VALUES( ‘Order’[Ship Date] ), ‘Order’[Ship Date] > MAX( Dates[Date] )))

Having the second filter statement set to >= pulled in all the orders that shipped on the last day of each month.

I can’t thank you enough … it took a little while to settle it into my model, but once I’d combed through all the measures to ensure that USERELATIONSHIP was set up correctly, and taken out any additional relationships in the Backlog Orders formula, then it works a treat.


#9

That’s great.