Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey there,
I have 2 tables for stock management. 1 for the list of stock and some other properties and 1 for the daily values (i have a relationship between both on the index of the stock).
I would like to have a weekly performance ie the value has increased/decreased by xx from the previous week.
So i created a table (weeklies) with a few rows which correspond to a week for each row. i have 2 columns : 1 is the begin date of the week, 1 is the last date of the week.
Im creating a calculated third column with the sum of all the values at the begin date of a given week :
= CALCULATE (
SUMX(Daily_Stock;[Price]*RELATED(Stock_list[Qty]) );
FILTER (Daily_Stock;
Daily_Stock[Date] =weeklies[begin_date] )
)
I works fine but i would like to exclude some stocks which were sold before the begin date (i have other reasons to be able to achieve this) so 'im trying to multiply by 0 if it is the case for that specific stock.
= CALCULATE (
SUMX(Daily_Stock;[Price]*RELATED(Stock_list[Qty]) * if(RELATED(Stock_list[sold_date]) < weeklies[begin date] ; 0 ;1) );
FILTER (Daily_Stock;
Daily_Stock[Date] =weeklies[begin_date] )
)
there i have the following error :
A single value for column sold_date in table Stock_list cannot be determined. Tweaking around a little bit and i had the same error on the weeklies table.
Does anyone know what i should be doing here ?
I can explain more, i wanted to avoid a too long post.
thanks
Solved! Go to Solution.
@Anonymous
The problem is related() function only work to refer to a 1 side table, I guess Stock_list[sold_date] column is a non-unique (repeated date). DAX RELATED TABLE FUNCTIONS - Data Bear - Power BI Training and Consulting.
If the weeklies[begin date] column is the 1 side table (unique date), you can replace the if function to the following expression. Otherwise, you need to use RELATEDTABLE function (DAX) - DAX | Microsoft Docs to compare.
if(Stock_list[sold_date] < related(weeklies[begin date]) ; 0 ;1)
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Anonymous
The problem is related() function only work to refer to a 1 side table, I guess Stock_list[sold_date] column is a non-unique (repeated date). DAX RELATED TABLE FUNCTIONS - Data Bear - Power BI Training and Consulting.
If the weeklies[begin date] column is the 1 side table (unique date), you can replace the if function to the following expression. Otherwise, you need to use RELATEDTABLE function (DAX) - DAX | Microsoft Docs to compare.
if(Stock_list[sold_date] < related(weeklies[begin date]) ; 0 ;1)
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.