Forum Discussion
Helper VHow to show only rows where a date range includes the selected date
Hi everyone,
I've got a table that contains all the orders placed to suppliers, with the ID, the OrderDate, the supplier's name, the Unit of Measure, the Quantity, the Unit Value and the total amount paid:
Then I've got a table containing suppliers' price lists. It contains the list of items and, for each one, the date range within which a certain price is valid:
ValidityEndingDate = 31/12/2099 means that the price is currently valid.
My intent is to create a dashboard in which the user can click on a certain order (in the first table) and get the price that was valid at the moment of the order (from the second table).
Thus, I want to visualize in the second table only the specific row in which 'Table1'[OrderDate] is contained in the interval between 'Table2'[PriceListValidityStartingDate] and 'Table2'[PriceListValidityEndingDate].
For example:
I hope it's all clear. Here is the PBIX file with data: https://www.dropbox.com/s/0uki6v3dqkardja/PRICELISTS%20suppliers.pbix?dl=0
Many thanks
mtrevisiol , Create all the measures like these or use that as visual level filter
var _max = maxx(allselected(Table1), Table[Order Date])
return
calculate( count(Table[Pirce]), filter(Table2, 'Table2'[PriceListValidityStartingDate] <= _max && 'Table2'[PriceListValidityEndingDate] >=_max))
2 Replies
- amitchandak
Super User
mtrevisiol , Create all the measures like these or use that as visual level filter
var _max = maxx(allselected(Table1), Table[Order Date])
return
calculate( count(Table[Pirce]), filter(Table2, 'Table2'[PriceListValidityStartingDate] <= _max && 'Table2'[PriceListValidityEndingDate] >=_max))
mtrevisiolThank you so much amitchandak
I've used MAX instead of COUNT:
var _max = maxx(allselected(Table1), Table[Order Date])
return
calculate( MAX(Table[Pirce]), filter(Table2, 'Table2'[PriceListValidityStartingDate] <= _max && 'Table2'[PriceListValidityEndingDate] >=_max))