Fill line chart with last know value - using date table

[Anonymous]

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_FROM is the starting point until PRICE_UNTIL)
I also use a simple Date Table to have a hierarchy of dates.

The line chart with the data looks like this:

I would like to have a line chart for every month of every year and fill the missing data with their last known value.
Checking the option "show items with no data" gives me this result, which is promising:

I would now need to link all those data points by creating missing one with the last know value.

I tried getting the last date in DAX with this formula:

Price evolution 2 = 
// Function: Get defined price or last know price from date

// Get current date in filter context
VAR current_date = 
    MAX(DF_Dim_Time[Date])

// Get last known date where value was entered
VAR last_known_date =
    CALCULATE(
        MAX(table_pricing[PRICE_UNTIL]),
        table_pricing[PRICE_UNTIL] <= current_date
        )
VAR t = 
// Get price from filter context
CALCULATE(
        SUM(table_pricing[PRICE]),
        DF_Dim_Time[Date] = last_known_date
    )
RETURN
last_known_date

But it does not work and I don't know if EARLIER or OFFSET could be beneficial... 

An important remark: We can't generate in Power Query the amount of data in the back-end (the price per month) since it would be hundreds of millions of rows.

Thank you in advance for the help
Cheers
John

[johnt75]

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]

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 · Accepted solution]

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]

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

[johnt75]

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]

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