topic Re: Create new column with calculated price per category in DAX Commands and Tips

Create new column with calculated price per category

Nic_2022 — Thu, 09 Jun 2022 02:21:04 GMT

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

Re: Create new column with calculated price per category

Jihwan_Kim — Thu, 09 Jun 2022 03:31:43 GMT

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

Re: Create new column with calculated price per category

Nic_2022 — Thu, 09 Jun 2022 04:33:55 GMT

Thank you so Much! 😀