Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

View solution in original post

5 REPLIES 5
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.