Excel Measure - Latest price applicable

[sebastiennn]

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

[p45cal · Accepted solution]

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…

[ZhangKun · Accepted solution]

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()
)

[sebastiennn]

Thanks a lot. I tested a lot of different measures without success.

It works perfectly. Thanks again !!

[ThxAlot]

[sunate]

=CALCULATE (
LASTNONBLANKVALUE ( 'Calendar'[Date], SUM ( Table1[Price] ) ),
ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX('Calendar'[Date]))

[p45cal · Accepted solution]

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…

[sebastiennn]

Thanks a lot!! It works, the next challenge is to understand the code 😀