LASTNONBLANKVALUE challenging problem

Hi All,

I have created an inventory data model where I calculate the inventory position at the end of each day. The dataset corresponds to a transactions table and not a snapshot table. I have used the LASTNONBLANKVALUE statement to get the expected result.

For each transaction, I have the ending balance value. I just need to get this value and populate it to the next day until there is another transaction.

Sometimes, there are more than one transaction each day, then I use the "sequence_no" to determine the lastest transaction.

To understand the model and the measure below, it is important to consider a unique product is composed of the following colums = warehouse / product_id / version_id 

The problem I have is that the value is not populated over all the dates and you can see in the below screenshot that some dates are missing.

The problem comes from the complexity of the data model. If you take a look at the measure I have created, I used a virtual table to make sure the LASTNONBLANKVALUE would iterate through the following columns: warehouse, product_id and version_id. The SUMX statement allows to get the correct total (column on the right).

Last Known Inventory = 
VAR LastKnownDate =
    MAX ( 'Date'[Date] )
VAR lastKnownInventory =
    CALCULATETABLE (
        SUMMARIZE (
            MyData,
            MyData[warehouse],
            MyData[product_id],
            MyData[version_id],
            "LastKnownInventory",
                CALCULATE (
                    LASTNONBLANKVALUE (
                        MyData[sequence_no],
                        SUMX ( MyData, MyData[ending_balance] )
                    ),
                    'Date'[Date] <= LastKnownDate
                )
        )
    )
RETURN 
   SUMX ( lastKnownInventory, [LastKnownInventory] )

Here is the PBIX.

Thanks for your help.