cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Dynamically calulated columns doesnt work

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)

 

 

06082018 Screen Aging stock.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Dynamically calulated columns doesnt work

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

5 REPLIES 5
Super User
Super User

Re: Dynamically calulated columns doesnt work

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
Not applicable

Re: Dynamically calulated columns doesnt work

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

 

 

Super User
Super User

Re: Dynamically calulated columns doesnt work

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
Not applicable

Re: Dynamically calulated columns doesnt work

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)

 

08082018 Power BI BG Report Screen FreshNes.PNG08082018 Power BI BG Report Screen Demand.PNG08082018 Power BI BG Report Screen Prizes.PNG

 

 

Thank you again for helping me

Radkos

 

 

 

Anonymous
Not applicable

Re: Dynamically calulated columns doesnt work

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