Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Fill line chart with last know value - using date table

Hello Community, I have an issue with creating a line chart with desired requirements: I have a single table for the data which has articles with their price during a certain date range (PRICE_FR...
Need Help
  • johnt75's avatar
    johnt75
    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
    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I marked the column INDEX from my table as a key column; it did make the error go away.

I tried to use only the column and no aggregation for the second argument of SUMX: it throws an error:

It seems your code is for a calculated column, am I correct ? For a measure, we would need an expression with an aggreagated function, right ?
A calculated column can't sadly be used IMHO, since we don't have the dates in the fact table, only the data ranges.

Now I am trying to use the measure still but it is not behaving as I would like. It still shows me only data points but does not fill the missing datapoints (example with just one article selected):

 

 

 

The line chart has been simply used with the option "show items with no data" as well:


Since it was not mentionned before, I am using a relationship between PRICE_UNTIL and Date, as shown here:

I also tried PRICE_FROM but no luck.

johnt75's avatar
johnt75
Super User
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
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Amazing ! 
    It did work as expected! 
    Thank you for your expertise.