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

Try

Last Known Price =
VAR RefDate =
    MAX ( 'Date'[Date] )
VAR Result =
    SUMX (
        INDEX (
            1,
            FILTER (
                'Table',
                'Table'[Price Until] >= RefDate
                    && 'Table'[Price From] <= RefDate
            ),
            ORDERBY (
                'Table'[Price Until], DESC,
                'Table'[Price From], DESC,
                'Table'[Index column]
            ),
            PARTITIONBY ( 'Table'[Article number] )
        ),
        'Table'[Price]
    )
RETURN
    Result

You will need to have a column which uniquely identifies each row. If you don't have one already you could use power query to add an index column. Make sure that in the modelling view you mark that column as the key column for the table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

    Thank you for the measure.
    I built an index with Power Query starting from 0.

    But I get an error.

     

    I don't quite understand it also, since I used an index from Power Query, why do it sees duplicates ?

    For the sake of completion, this is exactly what I have entered

    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] )
        ),
        SUM(tbl_price[PRICE])
    )
RETURN
    Result