# Power BI Exchange

View Only

## Show value from selected date range

• #### 1.  Show value from selected date range

Bronze Contributor
Posted Nov 01, 2022 07:10 AM
Hi.

I am trying to create a report showing the development in purchase prices over a given period. In inclosed report, I have the purchase prices in one table and a date table.
When I select a period (based on date table) from March 12 2021 to March 30 2022 I would like to see all puchase prices in this period. However, if I have a purchase price valid from January 1 2021 to April 30 2021 this price wont show in the report as the start date for the price is before the start date in the date table.

How can I solve this? The price is valid on March 12...

Pls. see enclosed file - I have used article "Test 4" in above description.

Helen

------------------------------
Helen Wrensted
------------------------------

Attachment(s)

Historic_Gross_Price.xlsx   11 KB 1 version
CostPrices.pbix   25.17 MB 1 version

• #### 2.  RE: Show value from selected date range

Posted 30 days ago

Hi Helen,

Here's what you should do:

a. Create another date table and connect to valid from column ---> this is just have your date always at the beginning of the month (you can use valid from date directly as well)
b. Create inactive relationship between main table and existing date table (connection: Valid to and Date)
c. Create a formula to incorporate the "valid to" values to your existing results (I removed the unnecessary formula btw).

PricePerStartOfMonth =

Var MaxDate = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'[Date]))
Var MinDate = CALCULATE(Min('Date'[Date]),ALLSELECTED('Date'[Date]))

VAR ValidTo =     CALCULATE (
MAX ( 'Historic_Gross_Price'[Price] ),
USERELATIONSHIP(Historic_Gross_Price[Valid to],'Date'[Date]),
MinDate>=Historic_Gross_Price[Valid from] && MinDate <=Historic_Gross_Price[Valid to])

RETURN

CALCULATE(MAX ( 'Historic_Gross_Price'[Price] ),'Date'[First day] = "Yes") +
ValidTo

------------------------------
Senior Finance Analyst
------------------------------

Attachment(s)

CostPrices (1).pbix   76 KB 1 version

• #### 3.  RE: Show value from selected date range

Bronze Contributor
Posted 30 days ago
Hi.

It works great! However, I have a problem with the prices that are effective from dates that are not the first day of the month. In enclosed file, I have added a simple matrix where we would like to see all cost prices in selected period. For article Test 4 it is fine, but if you look at article Test 3 (where some of the prices are effective from mid month), prices are missing.

How can this be soved?

------------------------------
Helen Wrensted
------------------------------

Attachment(s)

CostPrices (1).pbix   76 KB 1 version

• #### 4.  RE: Show value from selected date range Best Answer

Posted 30 days ago
Hi Helen, In that case, just remove the filter context in the formula.
PricePerStartOfMonth =

Var MaxDate = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'[Date]))
Var MinDate = CALCULATE(Min('Date'[Date]),ALLSELECTED('Date'[Date]))

VAR ValidTo =     CALCULATE (
MAX ( 'Historic_Gross_Price'[Price] ),
USERELATIONSHIP(Historic_Gross_Price[Valid to],'Date'[Date]),
MinDate>=Historic_Gross_Price[Valid from] && MinDate <=Historic_Gross_Price[Valid to])

RETURN

CALCULATE(MAX ( 'Historic_Gross_Price'[Price] )) +
ValidTo

------------------------------
Senior Finance Analyst
------------------------------

• #### 5.  RE: Show value from selected date range

Bronze Contributor
Posted 26 days ago
It works perfectly - thanks a lot!

------------------------------
Helen Wrensted
------------------------------

• #### 6.  RE: Show value from selected date range

Bronze Contributor
Posted 27 days ago
From the title of the measure, "Price per start of month", I assume that you want the price to be displayed not on the actual valid from date, but at the first month start date on or after the valid from date. In the attached file I have set up a solution based on that assumption.

------------------------------
Tomas
------------------------------

Attachment(s)

CostPrices 2.pbix   60 KB 1 version

• #### 7.  RE: Show value from selected date range

Bronze Contributor
Posted 26 days ago
Hi Tomas.

Thanks a lot for the solution, which works great for the first month start date.

------------------------------
Helen Wrensted
------------------------------

• #### 8.  RE: Show value from selected date range

Posted 27 days ago
Edited by Kaz Shakir 26 days ago
@Helen Wrensted,
I think that you should get the result that you want if you simply delete the relationship between your Date table and the Historic_Gross_Price table.  Then the measures you have created will give you the results that you are expecting.  So, your data model will simply look like this:

And after you delete that relationship, your resulting matrix will look like this:
Another approach would be to create a new historic gross price table, where the prices for each "Material" are shown for each date.  This new table will be a cross join between that dates of the date table and different material.  Here is the formula I used to create that table:
``````Daily_Historic_Gross_Price =
CROSSJOIN(
Calendar( Date(2017, 1, 1), Date(2022,3,30)),
VALUES(Historic_Gross_Price[Material])
)​``````
​Next, I would create calculated columns in this new Daily_Historic_Gross_Price table that show the price that's effective on that given date for that given material, and another to show the currency that is effective during that time as well.  Here are the formulas for those columns:
``````Effective_Price =
VAR currentDate = 'Daily_Historic_Gross_Price'[Date]
VAR currentMaterial = 'Daily_Historic_Gross_Price'[Material]
VAR filteredData =
FILTER(
Historic_Gross_Price,
Historic_Gross_Price[Material] = currentMaterial && Historic_Gross_Price[Valid from] <= currentDate && Historic_Gross_Price[Valid to] >= currentDate
)
VAR relatedPrice =
MAXX(
filteredData,
Historic_Gross_Price[Price]
)
RETURN
relatedPrice​``````
``````Effective_Currency =
VAR currentDate = 'Daily_Historic_Gross_Price'[Date]
VAR currentMaterial = 'Daily_Historic_Gross_Price'[Material]
VAR filteredData =
FILTER(
Historic_Gross_Price,
Historic_Gross_Price[Material] = currentMaterial && Historic_Gross_Price[Valid from] <= currentDate && Historic_Gross_Price[Valid to] >= currentDate
)
VAR relatedCurrency =
MAXX(
filteredData,
Historic_Gross_Price[Currency]
)
RETURN
relatedCurrency``````

Then, in your data model, you would connect the Date table to this new Daily_Historic_Gross_Price table:
Finally, in your table visualization, you would use the columns from the Date table and the Daily_Historic_Gross_Price table, so the result would look like this:

I am attaching my solution here for your review.
Please let us know if this gets you the results you were looking for.

Kaz.

------------------------------
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
------------------------------

Attachment(s)

CostPrices_r3.pbix   25.19 MB 1 version

• #### 9.  RE: Show value from selected date range

Bronze Contributor
Posted 26 days ago
Hi Kaz.

Thanks a lot for your solution - it works great!

------------------------------
Helen Wrensted