topic Re: How to return previous day's price based on irregular pricing intervals in DAX Commands and Tips

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

Anonymous — Thu, 30 Jul 2020 18:57:47 GMT

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!

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

lbendlin — Fri, 31 Jul 2020 02:57:58 GMT

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.

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

Anonymous — Fri, 31 Jul 2020 09:10:16 GMT

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

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

Anonymous — Fri, 31 Jul 2020 11:15:06 GMT

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"

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

Anonymous — Fri, 31 Jul 2020 13:19:35 GMT

Thank you Anonymous. 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"?

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

Fowmy — Fri, 31 Jul 2020 21:05:23 GMT

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

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

Anonymous — Sat, 01 Aug 2020 08:06:34 GMT

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)