Create new column with calculated price per category

[Nic_2022]

Hi,

I have a table of different materials which may or may not have a price change. I need to have a price for every month, but at present I only have a price and date when the price has changed, please see data below. I want to add a calculated column to this table which fills in the blanks with the latest Price for each Material according to the Date of Price Change. In the data below I have added what I want to see as the end column: ‘Latest Price’.  I have looked at lots of posts using Lastnonblank and earlier but these are not grouped by Category (i.e. Material in my case) and I can’t return what I’m looking for. Any help is much appreciated.

The first 4 columns is what I have in my table and the last column is what I’m trying to achieve.

Material	Start Month	Price Change	Date of Price Change	Latest Price
NZ.984	Jan-21	4.96	27/05/2020 0:00	4.96
NZ.984	Feb-21			4.96
NZ.984	Mar-21			4.96
NZ.984	Apr-21			4.96
NZ.984	May-21			4.96
NZ.984	Jun-21			4.96
NZ.984	Jul-21	4.92	1/07/2021 0:00	4.92
NZ.984	Aug-21			4.92
NZ.984	Sep-21			4.92
NZ.984	Oct-21			4.92
NZ.984	Nov-21			4.92
NZ.984	Dec-21	5.07	1/12/2021 0:00	5.07
NZ.984	Jan-22			5.07
NZ.984	Feb-22			5.07
NZ.984	Mar-22			5.07
NZ.433	Jan-21	1.87	27/05/2020 0:00	1.87
NZ.433	Feb-21			1.87
NZ.433	Mar-21	1.77	1/03/2021 0:00	1.77
NZ.433	Apr-21	1.77	1/04/2021 0:00	1.77
NZ.433	May-21			1.77
NZ.433	Jun-21	1.77	1/06/2021 0:00	1.77
NZ.433	Jul-21			1.77
NZ.433	Aug-21			1.77
NZ.433	Sep-21			1.77
NZ.433	Oct-21			1.77
NZ.433	Nov-21	1.77	1/11/2021 0:00	1.77
NZ.433	Dec-21			1.77
NZ.433	Jan-22			1.77
NZ.433	Feb-22			1.77
NZ.433	Mar-22			1.77

[Nic_2022]

Thank you so Much! 😀

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I assume your sample data model has the column type like the attached file.

The latest price CC =
VAR _currentstartmonth = Data[Start Month]
VAR _currentmaterial = Data[Material]
VAR _pricetable =
    FILTER (
        SUMMARIZE (
            Data,
            Data[Material],
            Data[Price Change],
            Data[Date of Price Change]
        ),
        Data[Material] = _currentmaterial
            && Data[Date of Price Change] <= _currentstartmonth
    )
VAR _pricetablelatestdate =
    MAXX ( _pricetable, Data[Date of Price Change] )
VAR _latestprice =
    SUMMARIZE (
        FILTER ( _pricetable, Data[Date of Price Change] = _pricetablelatestdate ),
        Data[Price Change]
    )
RETURN
    _latestprice