Forum Discussion
Helper IIFiltering Data Based on a Date Table and 2 Date Columns in a Second Table
Hey all,
I have a table with 2 date columns, DateBought and DateSold. Every item in the report will have a date bought value, since all items in the date were bought, but items that have not been sold will have a null DateSold table. I've made a mockup table below.
| ID | DateBought | DateSold |
| 1 | 1/1/2022 | 1/10/2022 |
| 2 | 1/3/2022 | |
| 3 | 1/6/2022 | 1/14/2022 |
| 4 | 1/7/2022 | |
| 5 | 1/10/2022 | 1/11/2022 |
| 6 | 1/15/2022 | 1/16/2022 |
| 7 | 1/17/2022 | |
| 8 | 1/20/2022 | 1/22/2022 |
| 9 | 1/22/2022 |
I want to create a slicer that will allow me to filter to if one of the columns is in the date range.
So, for example, if the date range is 1/10/2022 to 1/14/2022, it should return the columns 1, 3, and 5. From there, I want to count how many were bought in this time range (DateBought is in the range of the slicer) and how many were sold in this time range (DateSold is in the range of the slicer). For the example as before, there was 1 bought (5) and 3 sold (1, 3, and 5) during this time range.
Any advice is greatly appreciated. I can split the data so that its two separate tables with the same columns, just with the values already bought or sold split into separate tables.
2 Replies
- amitchandak
Super User
jarwest , You need to create a date table and join both dates with that. One join will be active and one will be inactive, you can use userelationship in measure to activate the join
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
jarwestWouldn't that filter if both dates are in range? I need to filter on if DateBought or DateSold are in this range, but if I understand this correctly it would filter on if DateBought and DateSold are in this range.
