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.

johnt75's avatar
johnt75
Super User
3 years ago

In the modelling view, select the table and then there's an option to select a key column. choose the index column you added, that should get rid of the error. the DAX needs tweaking slightly,

Last Known Price = 
VAR RefDate =
    MAX ( DF_Dim_Time[Date] )
VAR Result =
    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]
    )
RETURN
    Result

For the second parameter to SUMX you don't need SUM, just the column name.

  • 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.