Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 .... |
Solved! Go to 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
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
I've developed this one, which seems to be working but NOT with multiple items.
How can I solve the following problems with multiple items?
It's not executing separate calculation for different items, but aggregating them...
Thank you
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
Thank you for your answer. It's not contextualizing items still.
Here is the .pbix --> https://easyupload.io/rvndjm
BR,
Marco
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |