Inventory Needs to Be Zero Instead of Last Value

[JKarrick]

The daily inventory snapshot by material only includes rows when there is inventory. Currently the inventory in an Analysis Services tabular cube is overstated at the month level because it shows the last value for the time period. In the example below, material 392338 shows 21 for Dec 2023. However, when expanded to the day level the last day with inventory is Dec 20.  Instead, the Dec 2023 total should be zero. The current DAX calculation is below. Any suggestions on how to make it return a zero for any dates after Dec 20 rather  than showing 21? 

Current DAX for Inventory:

VAR SelectedPeriods =
VALUES ( 'Date'[date_key] )

VAR SelectedPeriodsSS =
VALUES ( Inventory[INVENTORY_LEVEL_DATE_DIM_KEY] )

VAR MaxPeriodID =
CALCULATE (
MAX ( Inventory[INVENTORY_LEVEL_DATE_DIM_KEY] ),
ALL ( 'Inventory' ),
SelectedPeriods,
SelectedPeriodsSS
)
RETURN
CALCULATE (
SUM ( Inventory[BATCH_WEIGHT_TONS] ),
Inventory[INVENTORY_LEVEL_DATE_DIM_KEY] = MaxPeriodID
)

[v-yaningy-msft]

Hi, @JKarrick 

Based on your description, is this what you need?

1.When the matrix is not expanded, change the value corresponding to 392338 in row Dec 2023 to 0.

2.After expanding the matrix, change all the values after 12/21 to 0 instead of blank values.

You can share the pbix file without sensitive data or the data and any related measures.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JKarrick]

Hi @v-yaningy-msft,

Thank you for the reply. You are correct in the desired results.

1. Yes, when the matrix is not expanded, change the value corresponding to 392338 in row Dec 2023 to 0.

2. Yes, after expanding the matrix, change all the values after 12/21 to 0 instead of blank values.

The pbix file data source is an analysis services cube and I'm using Tabular Editor to build the model so that's where the DAX code resides for inventory tons. The cube will be accessed by users via PBI and Excel, so I'm hoping to modify the model so any tool shows the desired results. The table below contains the sample data. In the model there is a also a date dimension that the inventory date from the fact table maps to. 

Date	Material Number	Inventory Tons
12/1/2023	344306	1203
12/1/2023	392338	24
12/2/2023	344306	1203
12/2/2023	392338	24
12/3/2023	344306	1203
12/3/2023	392338	24
12/4/2023	344306	1163
12/4/2023	392338	24
12/5/2023	344306	1102
12/5/2023	392338	24
12/6/2023	344306	1082
12/6/2023	392338	24
12/7/2023	344306	1082
12/7/2023	392338	24
12/8/2023	344306	1082
12/8/2023	392338	24
12/9/2023	344306	1082
12/9/2023	392338	24
12/10/2023	344306	1082
12/10/2023	392338	24
12/11/2023	344306	1082
12/11/2023	392338	24
12/12/2023	344306	1082
12/12/2023	392338	24
12/13/2023	344306	1082
12/13/2023	392338	24
12/14/2023	344306	1082
12/14/2023	392338	24
12/15/2023	344306	1082
12/15/2023	392338	21
12/16/2023	344306	1082
12/16/2023	392338	21
12/17/2023	344306	1082
12/17/2023	392338	21
12/18/2023	344306	1082
12/18/2023	392338	21
12/19/2023	344306	1082
12/19/2023	392338	21
12/20/2023	344306	1082
12/20/2023	392338	21
12/21/2023	344306	1082
12/22/2023	344306	1042
12/23/2023	344306	1042
12/24/2023	344306	1022
12/25/2023	344306	1022
12/26/2023	344306	1022
12/27/2023	344306	1002
12/28/2023	344306	1002
12/29/2023	344306	1110
12/30/2023	344306	1206
12/31/2023	344306	1206

[Jihwan_Kim]

Hi,

I am not sure how the semantic model looks like, but please try something like below whether it suits your requirement.

Current DAX for Inventory: =
VAR MaxPeriodID =
    MAX ( 'Date'[date_key] )
RETURN
    CALCULATE (
        SUM ( Inventory[BATCH_WEIGHT_TONS] ),
        'Date'[date_key] = MaxPeriodID
    )

[JKarrick]

Hi @Jihwan_Kim ,

I added '+ 0' to your DAX suggestion and that solved the issue in Dec 2023, but in Jan 2023 now it now shows zero for the total because not finished with the month and it's returning the value on the max day of the period. How can it return the max date key from yesterday since the inventory snapshots are from the prior day. 

Current DAX for Inventory Tons2: =
VAR MaxPeriodID =
    MAX ( 'Date'[date_key] )
RETURN
    CALCULATE (
        SUM ( Inventory[BATCH_WEIGHT_TONS] ) + 0,
        'Date'[date_key] = MaxPeriodID
    )

Thanks in advance for your help!

[JKarrick]

I've been out of the office a while but still have my issue so following up to see if any other suggestions @v-yaningy-msft @Jihwan_Kim 

The DAX calculation suggested returns the correct value for history, but since it uses the MAX of the Date Key it returns zero for the current month. The Feb 2024 total shows zero, but when expanded to the day level, there are 25 tons on 2/1/2024 so the Feb total should be 25. I would like it to use the current date - 1 day since inventory snapshots are one day behind.  

Thanks in advance for your help!