How to return previous day's price based on irregular pricing intervals

[Anonymous]

Hello,

I have a table of price that looks like the below, and I'd like to create a measure that returns the previous day's price (second row)

	1/1/2020	1/3/2020	1/7/2020	2/15/2020
Today's price	$10.00	$9.00	$8.00	$9.00
Yesterday's price		$10.00	$9.00	$8.00

I tried the following, but it return blank: 

calculate(AVERAGE('Price'[Price_Per_Unit]), previousday('Pricing Window'[Price Window Start Date]) )

I suspect it is because the previous day (e.g., 1/6/2020), doesn't exist in "Pricing Window".

How do I go about resolving the issue? If I can return the previous pricing window's price, that works too. Thanks a lot!

[Fowmy]

@Anonymous 

I have attached the PBIX and an Excel file with source data in the format you have, try entering more dates, prices, and refresh to check.

You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[harshnathani]

Hi @Anonymous ,

Try unpivoting your Table/

Paste this in Power Query Advance Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslPSayMKTUwMDIvVigoykxOVdJRUjE00DMwADEsobQFMj9WJ1opMrW4JLUIi14F3PpjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"1/1/2020" = _t, #"1/3/2020" = _t, #"1/7/2020" = _t, #"2/15/2020" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"1/1/2020", Currency.Type}, {"1/3/2020", Currency.Type}, {"1/7/2020", Currency.Type}, {"2/15/2020", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Price"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Price"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}, {"Price", "Day"}, {"Value", "Price"}})
in
    #"Renamed Columns1"

[Anonymous]

Thank you @harshnathani. However, my dates change as time passes, so I can't hard code them. Is there a way around it? Also, what do you mean by "unpivot the table"?

[harshnathani]

Hi @Anonymous ,

You do not need to hardcode anything.

Unpivoting your Table will get your Data in the correct order as you need.

Recommed you see this video to understand more about Unpivoting your Tables.

https://www.youtube.com/watch?v=hGj2axffxHo

https://www.youtube.com/watch?v=c6RJCg5XaRo

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

You want to make your columns rows and rows your columns. Then it'll be dead easy.

[lbendlin]

First thing you want to do is unpivot your table.  Then you can either use Power Query  (by merging the table with a copy of itself shifted by one row) or DAX with the regular "get data from previous row"  pattern.