Get price for any selected date within the Valid from and Valid to period?

[Alorz1]

Dear comunity, 

I have a Fact_Price_Tabel example below, and I created another Fact_Price_Table_Bridge (not sure if correct approcach), and connected the two tables base on the Price Key column in a many to many relation filter both ways. 

In the "bridge" table I add a evey date withn the price validity period, all dates. 

What I want to achive see below, I want to be able to work dynamically with dates have a "BASE" and "COMP" date slicers and be able to return the valid price for that selected date for both BASE and COMP so that I can later do price development calculations. 

So see below if I select BASE as 10.09.2022 the value shoud be 10 becasus  that price was valid during 22.08.2022 and 28.02.2023

and be able to select a COMP date as today date that will return the price 6 , so I can later do Price Dev = (COMP-BASE)/COMP and see the Price Development dynamiclay base on selected dates, is this posible to achive in Power BI with DAX?  

Many thanks, 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Alorz1]

Hi @lbendlin 

Thanks a lot for your replay! 

Let me try this. 

What I want to achive is a way to be able to compare the Price between different price periods using Date filter.  

I have a Valid From and Valid To, in the 2nd tabel I have add a Date representing every day in the Price period. You can notice the Date starts 1st of September because I work with Fiscal Year. 

But for the first price 0.0226 starting 22.08.2022 and valid until 28.02.2023 I have added the date for whole period. And maybe I have wrong approach... 

But what I want to achive is that I can select one Contract and one Product and Two different dates, one Base and one Comp, that will return the price valid that date, so if I select Base = "01.09.2022" I get Price 0.0226 and then I select Comp Date = "Today" then I get Price 0.0195 so then I can do a Price Development calcualtion like (Comp - Base)/Comp 

And this dynamicaly, so that I slect the date of 15.03.2023 for Base or Comp I will get the Price 0.0208

Thansks a lot, 

Emanuel 

Tabel with Price: 

Contract ID	Contract Version	Product	Price	PRICE_Tkey	Price Qty	Price Valid From	Price Valid To	Client WHS
ABC-0030301	1	102509	0.0226	13012734	100	2022-08-22 00:00	2023-02-28 00:00	420
ABC-0030301	2	102509	0.0208	13456680	100	2023-03-01 00:00	2023-03-31 00:00	420
ABC-0030301	3	102509	0.0195	13613562	100	2023-04-01 00:00	5999-12-31 00:00	420

Tabel with a date for every day within the Valid From and Valid To: 

Contract ID	Product	PRICE_Tkey	Price Valid From	Price Valid To	Client WHS	Date
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-01 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-02 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-03 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-04 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-05 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-06 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-07 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-08 00:00
ABC-0030301	102509	13012734	2022-08-22 00:00	2023-02-28 00:00	420	2022-09-09 00:00

[lbendlin · Accepted solution]

You can do this in a variety of ways. My approach would be to precalculate the price validity dates.

Tabel with Price:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc1BCsMwDATArxSfo7JaWa6VW5tnhPz/G1ENLcE9FMvG7IJm38vztQlgebQsZVzQEfnBHWR7J1nyYXWUyJdZCLqQN2D9RCagsH+jSpRjmQnOBPogqrfWcSVyX45OhInpH8ImQsMH0dS8cSLqlfCIEOUPcZw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Version" = _t, Product = _t, Price = _t, PRICE_Tkey = _t, #"Price Qty" = _t, #"Price Valid From" = _t, #"Price Valid To" = _t, #"Client WHS" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Price Valid From", type datetime}, {"Price Valid To", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Price Valid From", type date}, {"Price Valid To", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {Int32.From([Price Valid From])..Int32.From([Price Valid To])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type2"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".