Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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"
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"?
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |