LASTNONBLANK for each customer, ignoring other filters

[shadowsong42]

I am trying to determine the most recent inventory date for a customer, and use that to decide whether to reference the actual inventory or a calculation of projected inventory in a formula.

I have a Channel Inventory fact table with all sorts of fields - OrganizationID, SubisidiaryID, ProductID, DateID, Quantity.

I have dim tables for Organization, Customer, Subsidiary, Product, and Date.

I have a mapping table between fields in Organization and Customer.

I have a calculated measure that returns that inventory data mapped to customer instead of organization:

Channel Inventory Qty (Customer) = 
CALCULATE(
    'Channel Inventory'[Channel Inventory Quantity],
    TREATAS(
        VALUES('CFO Account Level'[Top Parent Name]),
        Organization[Top Parent Name]
    ),
    REMOVEFILTERS('CFO Account Level'[Customer Group Name]),REMOVEFILTERS(Customer[Customer Group Name])
)

I have measures calculating last week's inventory, and projected inventory:

Last Week Inventory = 
VAR LastWeek = max('Date'[Fiscal Week])-7
RETURN
CALCULATE([Channel Inventory Qty (Customer)],
    REMOVEFILTERS('Date'),KEEPFILTERS('Date'[Fiscal Week]=LastWeek)
)

Projected Inventory = 
[Last Week Inventory] + [Last Week SI] - [Actuals + CPFR Forecast Sell Thru Unlocked (Units)]

I am trying to write a measure that would replace [Last Week Inventory] in the Projected Inventory formula:

Last Week Inventory or Projection = 
VAR LastWeek = MAX('Date'[Fiscal Week])-7
VAR LastInventory = LASTNONBLANK('Date'[Fiscal Week],'Channel Inventory'[Channel Inventory Quantity])
RETURN 
CALCULATE(
	IF(
		LastInventory >= LastWeek,
		[Channel Inventory Qty (Customer)],
		[Projected Inventory]
	),
    REMOVEFILTERS('Date'),KEEPFILTERS('Date'[Fiscal Week]=LastWeek)
)

The part I am finding tricky is that I want to calculate LastInventory for the Customer as a whole, without breaking down by product, subsidiary, or any of the other fields in the Channel Inventory table or in page filters and visuals.

The TREATAS() relationship between Customer and Organization might also pose a problem. Maybe I need to duplicate that in the code for the variable? (Note that [Last Week SI] and [Actuals + CPFR Forecast Sell Thru Unlocked (Units)] are associated with Customer - only Channel Inventory Qty is associated with Organization.)

And of course I don't know if the recursion is going to be a problem, too.

And yet another tricky part: only occasionally is a customer's inventory reporting delayed. Currently, no one is late, so I can't actually verify whether or not the part of the calculation that chooses projected inventory when actual inventory is missing, is actually working.

So, I have two questions:

1. Will the code as written do what I expect, which is to choose whether to use actual inventory or projected inventory for each customer&subsidiary&product combination? If not, how do I fix it?
2. How do I make the code choose actual vs projected for each customer as a whole?

[rohit1991]

Hi @shadowsong42 

You're on the right path conceptually, but the issue lies in the context evaluation of your LastInventory variable. When using LASTNONBLANK as you've done, it is still sensitive to the current evaluation context—particularly filters on product, subsidiary, or other related fields—so it doesn't calculate at the customer level unless explicitly instructed. To get around this, you need to remove those additional dimensions from the evaluation so that LastInventory reflects the latest inventory date per customer only, regardless of other filters. You can achieve this by applying ALLEXCEPT() to retain only the customer-level context (or whatever defines a customer uniquely in your model). Additionally, since you're using TREATAS() in your [Channel Inventory Qty (Customer)] measure, you may need to mimic that logic to ensure consistency across related measures. Here’s a refined version of your logic that calculates the latest inventory date at the customer level and switches to projected inventory accordingly:

Last Week Inventory or Projection = 
VAR LastWeek = MAX('Date'[Fiscal Week]) - 7
VAR LastInventory = 
    CALCULATE(
        LASTNONBLANK('Date'[Fiscal Week], 'Channel Inventory'[Channel Inventory Quantity]),
        REMOVEFILTERS('Date'),
        ALLEXCEPT(Customer, Customer[Customer ID])  -- or substitute with the correct customer identifier
    )
RETURN
CALCULATE(
    IF(
        LastInventory >= LastWeek,
        [Channel Inventory Qty (Customer)],
        [Projected Inventory]
    ),
    REMOVEFILTERS('Date'),
    KEEPFILTERS('Date'[Fiscal Week] = LastWeek)
)

This structure ensures that LastInventory is determined purely at the customer level, unaffected by other slicers or visuals filtering by product, subsidiary, or other columns. Be sure that your relationships and the mapping logic in TREATAS() are correctly aligned in both [Channel Inventory Qty (Customer)] and this measure. Once you have actual late data, you can validate the switching logic; in the meantime, consider simulating a delay by excluding recent weeks from one customer's inventory to test the projected fallback path.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[shadowsong42]

1. How do I test this using DAX Studio? I can't figure out where the various DEFINEs and EVALUATEs should go.
2. Channel Inventory Quantity is not associated with customer. Can I use [Channel Inventory Qty (Customer)] in the LastInventory variable instead?
3. Is recursion going to be a problem, if [Last Week Inventory or Projection] references [Projected Inventory], and [Projected Inventory] references [Last Week Inventory or Projection]? Remember, the new formula for [Projected Inventory] is 

[Last Week Inventory or Projection] + [Last Week SI] - [Actuals + CPFR Forecast Sell Thru Unlocked (Units)]​

[v-pagayam-msft]

Hi @shadowsong42 ,
Thank you @rohit1991 and @danextian for the prompt response!

1.Testing in DAX Studio: You may use DEFINE and EVALUATE to try out your logic outside of visuals. The example from @rohit1991 code.

2.Using [Channel Inventory Qty (Customer)]: Yes, it makes sense to use that in your LastInventory variable since it aligns with the customer context you are targeting.

3.Recursion concern: You are right to flag this. If [Projected Inventory] refers back to [Last Week Inventory or Projection], that creates a circular reference. A good workaround is to separate the logic.,define a [Projected Inventory Base] measure, and then use that in your switching logic.

To test fallback behavior, you may temporarily filter out the most recent inventory data for a test customer and see how your measure reacts.

Hope this helps! If so,consider accepting it as solution.

Regards,
Pallavi.

[shadowsong42]

Here is some sample data, showing how I want the calculation to work based on whether there is inventory available in the previous week. Basically, for each week, I need to be able to reference the calculation from the previous week.

Can you tell me how to accomplish this?

Fiscal Week	Inventory	SI	ST	Projected formula	Projected result
4/25/2025	75,957	1,081	6,624	prev inv + prev si - this st	76,644
5/2/2025	71,664	5,791	7,366	prev inv + prev si - this st	69,672
5/9/2025	71,438	10,513	7,884	prev inv + prev si - this st	69,571
5/16/2025		14,569	9,616	prev inv + prev si - this st	72,335
5/23/2025		11,571	12,285	prev proj + prev si - this st	74,619
5/30/2025		11,508	12,174	prev proj + prev si - this st	74,016

[shadowsong42 · Accepted solution]

Holy crap, I figured it out. Instead of building each week on the previous week, I need to build each week on the last inventory and the sum of SI or ST for all the weeks between last inventory and now. Using this method, you can calculate an arbitrary future week without needing to figure out each week in between.

Here's the code:

Projected Inventory = 
VAR CurrentWeek = MAX('Date'[Fiscal Week])
VAR LastInventory = 
    CALCULATE(
        LASTNONBLANK( 
            'Date'[Fiscal Week], 
            'Americas Retail'[Channel Inventory Qty (Customer)]
        ),
        REMOVEFILTERS( 'Date' ), 
        REMOVEFILTERS( 'Product' ), 
        REMOVEFILTERS( 'Subsidiary' ), 
        KEEPFILTERS( 'Date'[Fiscal Week] < CurrentWeek) 
    )

RETURN
CALCULATE( // Inventory from the calculated LastInventory date
    'Americas Retail'[Channel Inventory Qty (Customer)],
	REMOVEFILTERS( 'Date' ),
	KEEPFILTERS( 'Date'[Fiscal Week] = LastInventory )
)+
CALCULATE( // Sell In from the LastInventory date through to the week before CurrentWeek
	[Actuals + CPFR Forecast Sell In Unlocked (Units)],
	REMOVEFILTERS( 'Date' ),
	KEEPFILTERS( 'Date'[Fiscal Week] >= LastInventory && 'Date'[Fiscal Week] < CurrentWeek)
) -
CALCULATE( // Sell Thru from the week after the LastInventory date through to the CurrentWeek
	[Actuals + CPFR Forecast Sell Thru Unlocked (Units)],
	REMOVEFILTERS( 'Date' ),
	KEEPFILTERS( 'Date'[Fiscal Week] > LastInventory && 'Date'[Fiscal Week] <= CurrentWeek)
)

I'll need to double check the LastInventory calculation the next time someone is late submitting their data - right now all Customers correctly show the same LastInventory date. But other than verifying that one little edge case, it runs and produces the correct results!

[danextian]

Hi @shadowsong42 

The issue with this variable is that it is affected by the query context introduced when adding or removing fields to a visual.

VAR LastInventory = LASTNONBLANK('Date'[Fiscal Week],'Channel Inventory'[Channel Inventory Quantity])

Changed it to something like this:

VAR LastInventory = 
CALCULATE (
    LASTNONBLANK (
        'Date'[Fiscal Week],
        'Channel Inventory'[Channel Inventory Quantity]
    ),
    ALLEXCEPT ( tbl, tbl[customer] )
)

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.