Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help with Recursive Measure

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.


t0 (today)12
ITEM A   
ORDER QTY200100400
STOCK [LQNTY]1000blankblank
MEASURE800700300
ITEM B ....   
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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






     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Anonymous ,
    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