Forum Discussion
Excel Measure - Latest price applicable
Hi,
For the last 2 days, I have tried to find the measure I should use for this problem (without success obviously 🙂
So I have a table with 3 colums: Article, Price and Date_of_change. So the table contains the change of prices of articles during the year 2024. Sometimes for 1 article there is no change of price for several months: the datas will be empty for these months.
| Article | Date | Price |
| A | 01-01-24 | 100 |
| A | 01-02-24 | 105 |
| A | 01-04-24 | 115 |
| B | 01-01-24 | 150 |
| B | 01-03-24 | 155 |
| B | 01-04-24 | 165 |
| C | 01-02-24 | 205 |
| C | 01-04-24 | 215 |
| D | 01-03-24 | 100 |
I would like to create a measure allowing me to show in a pivot table the last price applicable for every end of month.
The results should be:
| Article | 31-01-24 | 29-02-24 | 31-03-24 | 30-04-24 |
| A | 100 | 105 | 105 | 115 |
| B | 150 | 150 | 155 | 165 |
| C | 205 | 205 | 215 | |
| D | 100 | 100 |
But I always retrieve the following result
| Article | 31-01-24 | 29-02-24 | 31-03-24 | 30-04-24 |
| A | 100 | 105 | 115 | |
| B | 150 | 155 | 165 | |
| C | 205 | 215 | ||
| D | 100 |
It looks like the relationship is not correct. Not sure about it.
I create in powerpivot a data_table from 1st jan 24 to 31st Dec (all dates) and add a field End_of_month for my pivot table. I create a relation between the Field Date (all dates) and the date from the Price_db
Could you please help me? What is the measure or/and the relationship that I should use?
Thanks a lot
Séb
You've posted your question in the Power Query section so I answer with that (no measures).
Paste this into the advanced editor of PQ:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjEyDH0MBAKVYHIWGEkDBFkTCBSxhCJJwwjDI1QJEwRkig6kAYZQaRcEa33MgAVQKuwwhquQuGHSB/xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Price = _t]), fnEOMPrices=(tbl)=> let #"Changed Type1" = Table.TransformColumnTypes(tbl,{{"Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Price"}), #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Article] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Article"}) in #"Removed Columns", ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}), Latest = List.Max(ChangedType[Date]), Earliest = List.Min(ChangedType[Date]), EsOM = List.Transform(List.Generate(()=>Earliest, each _ <= Latest, each Date.AddMonths(_, 1)),Date.EndOfMonth), TblEsOM = Table.FromList(EsOM, Splitter.SplitByNothing(),{"Date"}, null, ExtraValues.Error), #"Grouped Rows" = Table.Group(ChangedType, {"Article"}, {{"grp", each _ & TblEsOM}}), #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "EomPrices", each fnEOMPrices([grp])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"grp"}), #"Expanded EomPrices" = Table.ExpandTableColumn(#"Removed Columns", "EomPrices", {"Date", "Price"}, {"Date", "Price"}), #"Filtered Rows" = Table.SelectRows(#"Expanded EomPrices", each ([Price] <> null)), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Price", type number}}) in #"Changed Type"It produces a table thus:
which can be pivoted, by (1) as a last step in PQ or (2) by adding a pivot table based on that table or (3) by loading to Pivot Table report. Whichever, the pivot ends up like:
The code can no doubt be more elegant and efficient…
The LASTNONBLANKVALUE function does not exist in some versions of Excel (and possibly all versions).
DAX measure formula:
VAR vCurrentDate = MAX('DT'[Date]) RETURN IF( vCurrentDate = EOMONTH(vCurrentDate, 0), CALCULATE( MAX('CT'[Price]), LASTNONBLANK('DT'[Date] <= vCurrentDate, CALCULATE(MAX('CT'[Price]))) ), BLANK() )
6 Replies
You've posted your question in the Power Query section so I answer with that (no measures).
Paste this into the advanced editor of PQ:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjEyDH0MBAKVYHIWGEkDBFkTCBSxhCJJwwjDI1QJEwRkig6kAYZQaRcEa33MgAVQKuwwhquQuGHSB/xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Price = _t]), fnEOMPrices=(tbl)=> let #"Changed Type1" = Table.TransformColumnTypes(tbl,{{"Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Price"}), #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Article] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Article"}) in #"Removed Columns", ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}), Latest = List.Max(ChangedType[Date]), Earliest = List.Min(ChangedType[Date]), EsOM = List.Transform(List.Generate(()=>Earliest, each _ <= Latest, each Date.AddMonths(_, 1)),Date.EndOfMonth), TblEsOM = Table.FromList(EsOM, Splitter.SplitByNothing(),{"Date"}, null, ExtraValues.Error), #"Grouped Rows" = Table.Group(ChangedType, {"Article"}, {{"grp", each _ & TblEsOM}}), #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "EomPrices", each fnEOMPrices([grp])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"grp"}), #"Expanded EomPrices" = Table.ExpandTableColumn(#"Removed Columns", "EomPrices", {"Date", "Price"}, {"Date", "Price"}), #"Filtered Rows" = Table.SelectRows(#"Expanded EomPrices", each ([Price] <> null)), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Price", type number}}) in #"Changed Type"It produces a table thus:
which can be pivoted, by (1) as a last step in PQ or (2) by adding a pivot table based on that table or (3) by loading to Pivot Table report. Whichever, the pivot ends up like:
The code can no doubt be more elegant and efficient…
Thanks a lot!! It works, the next challenge is to understand the code 😀
=CALCULATE (
LASTNONBLANKVALUE ( 'Calendar'[Date], SUM ( Table1[Price] ) ),
ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX('Calendar'[Date]))The LASTNONBLANKVALUE function does not exist in some versions of Excel (and possibly all versions).
DAX measure formula:
VAR vCurrentDate = MAX('DT'[Date]) RETURN IF( vCurrentDate = EOMONTH(vCurrentDate, 0), CALCULATE( MAX('CT'[Price]), LASTNONBLANK('DT'[Date] <= vCurrentDate, CALCULATE(MAX('CT'[Price]))) ), BLANK() )Thanks a lot. I tested a lot of different measures without success.
It works perfectly. Thanks again !!
