Dynamically calulated columns doesnt work

[Anonymous]

Hi all,

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)

[Stachu]

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
https://msdn.microsoft.com/en-us/query-bi/dax/selectedvalue-function

[Anonymous]

Hi Stachu,

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

Radkos

[Stachu]

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

[Anonymous]

Hi Stachu,

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

Radkos

[Anonymous · Accepted solution]

Hi, I found answer by myself, I megred all the table into one and then i used CALCULATE and ALLEXCEPT functions