Forum Discussion
Fill line chart with last know value - using date table
- 3 years ago
The red line under PRICE isn't really an error, its just Intellisense playing up, it does that sometimes.
The code is intended for use as a measure, but the SUMX is performing the aggregation, you don't need another aggregation function inside it.
I think the problem is the relationship with the date table, try
Last Known Price = VAR RefDate = MAX ( DF_Dim_Time[Date] ) VAR Result = CALCULATE ( SUMX ( INDEX ( 1, FILTER ( tbl_price, tbl_price[PRICE_UNTIL] >= RefDate && tbl_price[PRICE_FROM] <= RefDate ), ORDERBY ( tbl_price[PRICE_UNTIL], DESC, tbl_price[PRICE_FROM], DESC, tbl_price[Index] ), PARTITIONBY ( tbl_price[ARTPREINH] ) ), tbl_price[PRICE] ), REMOVEFILTERS ( DF_Dim_Time ) ) RETURN Resultor you could remove the relationship entirely if you don't need it for anything else. What the relationship is doing is filtering only those records with a price from ( or to ) date which exactly matches what is in the date table, and that isn't really useful for records which span multiple dates.
The red line under PRICE isn't really an error, its just Intellisense playing up, it does that sometimes.
The code is intended for use as a measure, but the SUMX is performing the aggregation, you don't need another aggregation function inside it.
I think the problem is the relationship with the date table, try
Last Known Price =
VAR RefDate =
MAX ( DF_Dim_Time[Date] )
VAR Result =
CALCULATE (
SUMX (
INDEX (
1,
FILTER (
tbl_price,
tbl_price[PRICE_UNTIL] >= RefDate
&& tbl_price[PRICE_FROM] <= RefDate
),
ORDERBY (
tbl_price[PRICE_UNTIL], DESC,
tbl_price[PRICE_FROM], DESC,
tbl_price[Index]
),
PARTITIONBY ( tbl_price[ARTPREINH] )
),
tbl_price[PRICE]
),
REMOVEFILTERS ( DF_Dim_Time )
)
RETURN
Result
or you could remove the relationship entirely if you don't need it for anything else. What the relationship is doing is filtering only those records with a price from ( or to ) date which exactly matches what is in the date table, and that isn't really useful for records which span multiple dates.
Amazing !
It did work as expected!
Thank you for your expertise.