Need Help with Recursive Measure

[marcoproserpio]

Hello,
I need help with the following recursive measure, I've been trying unsuccessfully something like this:

Measure =
var x = CALCULATE(sum(GIACENZA[LQNTY]) , GIACENZA[ITNBR] = max('ITMRVA chiave'[ITNBR])) - sum(ORDERS[ORDER QTY])
return
CALCULATE ( x , FILTER ( ALL('Calendar'), 'Calendar'[Date] <= MAX ('Calendar'[Date] ) ) )

Thank you.

t	0 (today)	1	2
ITEM A
ORDER QTY	200	100	400
STOCK [LQNTY]	1000	blank	blank
MEASURE	800	700	300
ITEM B ....

[marcoproserpio]

Hello @v-yiruan-msft ,
I was trying to figure out if it was possible to:
- Show measure values in dates where I'm not having Orders/Stock
- Filter the measure for negative values (i.e. Delta <0)  without breaking up the logic behind the Measure.
BR,
Marco

[marcoproserpio]

I've developed this one, which seems to be working but NOT with multiple items.

Measure = CALCULATE (-sum(ORDERS[LQRES])+sum(GIACENZA[LQNTY]),

FILTER(ALLSELECTED('ORDERS'), IF(TODAY()>FIRSTDATE(ORDERS[Data Consegna]), TODAY(), ORDERS[Data Consegna]) <= max('Calendar'[Date]) ))

How can I solve the following problems with multiple items?

It's not executing separate calculation for different items, but aggregating them...

Thank you

[v-yiruan-msft]

Hi @marcoproserpio ,

Please update the formula of your measure as below and check whether that can return the correct result.

Measure =
SUM ( GIACENZA[LQNTY] )
    - CALCULATE (
        SUM ( ORDERS[LQRES] ),
        FILTER (
            ALLSELECTED ( 'ORDERS' ),
            IF (
                TODAY () > FIRSTDATE ( ORDERS[Data Consegna] ),
                TODAY (),
                ORDERS[Data Consegna]
            )
                <= MAX ( 'Calendar'[Date] )
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables GIACENZA and ORDERS (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[marcoproserpio]

Thank you for your answer. It's not contextualizing items still. 
Here is the .pbix --> https://easyupload.io/rvndjm
BR,
Marco

[v-yiruan-msft · Accepted solution]

Hi @marcoproserpio ,

I updated your sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of Measure as below:

MEASURE = 
VAR _selitnbr =
    SELECTEDVALUE ( 'ITEM_KEY'[ITNBR] )
VAR _stqty =
    CALCULATE (
        SUM ( 'STOCK'[STOCKQTY] ),
        FILTER ( ALLSELECTED ( 'STOCK' ), 'STOCK'[ITNBR] = _selitnbr )
    )
VAR _minstdate =
    CALCULATE (
        MIN ( 'STOCK'[Date] ),
        FILTER ( ALLSELECTED ( 'STOCK' ), 'STOCK'[ITNBR] = _selitnbr )
    )
VAR _maxorddate =
    CALCULATE (
        MAX ( 'ORDERS'[Date] ),
        FILTER ( ALLSELECTED ( 'ORDERS' ), 'ORDERS'[ITNBR] = _selitnbr )
    )
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _culordqty =
    CALCULATE (
        SUM ( 'ORDERS'[ORDERSQTY] ),
        FILTER (
            ALLSELECTED ( 'ORDERS' ),
            'ORDERS'[ITNBR] = _selitnbr
                && 'ORDERS'[DATE] <= _seldate
        )
    )
RETURN
    IF (
        _seldate < _minstdate
            || _seldate > _maxorddate,
        BLANK (),
        CALCULATE ( SUM ( STOCK[STOCKQTY] ), ALLSELECTED ( 'Calendar'[Date] ) ) - _culordqty
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[marcoproserpio]

Hello @v-yiruan-msft ,
Works perfectly! 
Thank you.
BR,
Marco