Last value known per category association

[Anonymous]

Hello,

First of all I would like to thanks you for your help and apologize for my english, this is not my native language.

I am working on a Power BI desktop and I am struggling on a measure I am trying to create, but let me first introduce you to (what look like) my model : 

So my data are like :

CUSTOMER table:

ITEM table:

MOVEMENT table:

Let me skip the Calendar table, which I think is pretty clear for everyone 😉
Now what I am trying to do : a measure that give the lastest known state of my stock. 
So :

- If we have a data for the selected date, we pick the value

- If we don't have a value, we look for the lastest one.

Exptected : 
If I pick the 6th of November :

If I pick the 4th of November :

I can provide de different measure I tried, but since they are mainly tuned by ChatGPT... I don't think it will help a lot....
Thank you again for your help !

[Jihwan_Kim]

Hi, I am not 100% sure about how the fact table looks like, but if there are some missing dates in the fact table, below is one of many ways to achieve it.

Lastest date with stocks: = 
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            SUMMARIZE ( Movement, 'Calendar'[Date], Movement[stock_qty] ),
            'Calendar'[Date] < MAX ( 'slicer calendar'[Date] )
                && Movement[stock_qty] <> 0
        ),
        'Calendar'[Date]
    )
RETURN
    _lastnonblankdate

Lastest known stocks: = 
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            SUMMARIZE ( Movement, 'Calendar'[Date], Movement[stock_qty] ),
            'Calendar'[Date] < MAX ( 'slicer calendar'[Date] )
                && Movement[stock_qty] <> 0
        ),
        'Calendar'[Date]
    )
RETURN
    CALCULATE ( SUM ( Movement[stock_qty] ), 'Calendar'[Date] = _lastnonblankdate )