Keep all previous and current items & values in table regardless of date filter

[LakshX413]

Hi All - I am running into a weird issue: I have a requirment to show previous inventory and current inventory in a view for comparision. I created a matrix to lay down inventory by Platform (PLx) -> Brand (BRx) -> SKU (Px) and keep two columms in it, current inventory and previous inventory. Current inventory is from the data as-is and previous inventory is a meausre calculatd using:

previous_inventory =
VAR SelectedDate = SELECTEDVALUE('Units by Platform 1'[week_start_date])
VAR PreviousDate =
CALCULATE(
MAX('Units by Platform 1'[week_start_date]),
'Units by Platform 1'[week_start_date] < SelectedDate
)
RETURN
(
CALCULATE(SUM('Units by Platform 1'[Inventory]),
'Units by Platform 1'[week_start_date] = PreviousDate)
)

My issue is there are some SKUs that were sold but do not show up under previous inventory: See right viz - 15th March - P15,P16 were sold but do not show up under previous inventory (see left viz) when filtered to 16th March. What do I need to change in my measure to ensure all inventory shows up under previous inventory?

Note - I also tried few experiments: One was read in distinct SKU names from SKU table and another was read in dates from a date table instead of same inventory table, both did not work. 

[danextian · Accepted solution]

Hi @LakshX413 

This is one of the reasons why using separate dimensions tables is necessary.

Use a separate week_start_date dimension table. You can create a calculated table with this

weekdimensiontable = DISTINCT ('Units by Platform 1'[week_start_date])

Create a separate dimension table for platform units to ensure they remain unaffected by week filters. If columns from the fact table are used, only rows within the selected date range will be displayed in visuals. This means that if P15 has no data for March 16, it will not appear.

Create a one-to-many single direction relationship from these dim  tables to your fact and use these column in your visuals. Use this measure:

previous_inventory =
VAR PreviousDate =
    CALCULATE (
        -- Find the most recent week_start_date before the current one
        MAX ( 'weekdimensiontable'[week_start_date] ),
        FILTER (
            ALL ( 'weekdimensiontable' ),
            -- Consider all dates in the dimension table
            'weekdimensiontable'[week_start_date]
                < MAX ( 'weekdimensiontable'[week_start_date] ) -- Only include dates earlier than the current one
        )
    )
RETURN
    (
        CALCULATE (
            -- Retrieve the inventory sum for the identified previous week
            SUM ( 'Units by Platform 1'[Inventory] ),
            'dimensiontable'[week_start_date] = PreviousDate
        )
    )

If this doesn't work,   please provide a workable sample data (not an image). You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Please refer to this sticky post on how to your questions answered quickly - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[LakshX413]

Hi @danextian  - This worked perfectly. Thank you so much for your help!!

[Chaithra_A]

Hello @LakshX413 ,
You can try to modify the previous_inventory measure to account for the inventory of the SKU on the previous date, regardless of whether there is data for the SKU on the selected date. You can try this below measure that can help the previous inventory to be displayed even if the SKU is not sold on the selected date:

previous_inventory =
VAR SelectedDate = SELECTEDVALUE('Units by Platform 1'[week_start_date])
VAR PreviousDate =
CALCULATE(
MAX('Units by Platform 1'[week_start_date]),
'Units by Platform 1'[week_start_date] < SelectedDate,
ALL('Units by Platform 1') 
)
VAR PreviousInventory =
CALCULATE(
MAX('Units by Platform 1'[Inventory]), -- Using MAX to get the maximum inventory for the previous date
'Units by Platform 1'[week_start_date] = PreviousDate,
ALL('Units by Platform 1') 
)
RETURN
IF(
ISBLANK(PreviousInventory),
0,
PreviousInventory
)

Thanks.

[LakshX413]

Thanks @Chaithra_A. When I tried above, the data changed to only showing the max value across all previous inventory which are not correct values, see below.

[LakshX413]

@amitchandak - Any suggestions?

and unrelated shout out - Your date table solutions have tremedously helped me in the past! Thank you for those.