I want to make a financial calculation. I have got 3 Tables, one with available product on stock, second one with demand for every product and every week, third with price for every material i have. What i want to do is show how much from this part of stock(aging stock) i can sell considering that i can sell this stock in x weeks (for example 4 weeks). I created calculated column in the third table calculating aging stock where i have minimum and maximum freshness(every row has freshness column 0-100), where this minimum and maximum are parameters i can choose, but no matter what i choose the measure for column take value, how to change it?
There are my measures:
Aging Value = CALCULATE(SUM('FreshNes of Inventory'[Value]),Filter('FreshNes of Inventory',[Attribute2]>='Min Aging'[Min Aging Value]),Filter('FreshNes of Inventory',[Attribute2]<'Max Aging'[Max Aging Value]),FILTER('FreshNes of Inventory',[Material]=EARLIER('Prices'[Material])),FILTER('FreshNes of Inventory',[Company code]=EARLIER('Prices'[Company code])))
Max Aging = GENERATESERIES(60, 90, 5)
Min Aging = GENERATESERIES(50, 80, 5)
Solved! Go to Solution.
I assume [Attribute2] is column in 'FreshNes of Inventory', correct?
Aging Value = CALCULATE ( SUM ( 'FreshNes of Inventory'[Value] ), FILTER ( 'FreshNes of Inventory', 'FreshNes of Inventory'[Attribute2] >= SELECTEDVALUE ( 'Min Aging'[Min Aging Value] ) && 'FreshNes of Inventory'[Attribute2] < SELECTEDVALUE ( 'Max Aging'[Max Aging Value] ) ), FILTER ( 'FreshNes of Inventory', [Material] = EARLIER ( 'Prices'[Material] ) ), FILTER ( 'FreshNes of Inventory', [Company code] = EARLIER ( 'Prices'[Company code] ) ) )
basically I added SELECTEDVALUE around your parameter tables, to get the value from slicer
I tried it already, I thought like you that this should work, but after adding SELECTEDVALUE function all column is blank, there are no values at all.
Is there a posibility that calculated column can't have dynamic values?
Thank you for helping me
sorry I missed the fact that this is supposed to be calculated column. Columns are only evaluated once, they cannot refer to the parameter, but in most cases it should be possible to achieve the same result with a measure
can you post here sample anonymized data from the 3 tables? just top few rows to see the structure
Actually, my goal here is to filter out sum of between dynamically changing min and max Attribute(1), and then calculate Demand for dynamically changing number of weeks in future(2), then I want to do (1)-(2) and multiply by prize.
The point is that I want to do it for every material/Company Code separately, as they have different prizes.
Here is a part of my data (note that for every material there is also few different Company Codes)
Thank you again for helping me