Hello. I am fairly new with PowerBI and this is my first post. I am struggling with creating a measure from a many to many relationship. I have a table that has a date series for each day that a requisition was open to use as a timeline. I have another table with timelines (ranges) of when that requstion was posted. What I am trying to do is count the requisition as posted on a prticular day if the date falls between one of the posted stints from the timeline table. I could just fill the date series for each stint, but I don't want to make my data model to complicated so I'm hoping there is an alternative.
Table 1 - Open Timeline
| REQ_NUMBER | FLAG | START_TIME |
| 238277 | OPEN | 3/23/2022 0:00 |
| 238277 | OPEN | 3/24/2022 0:00 |
| 238277 | OPEN | 3/25/2022 0:00 |
| 238277 | OPEN | 3/26/2022 0:00 |
| 238277 | OPEN | 3/27/2022 0:00 |
| 238277 | OPEN | 3/28/2022 0:00 |
| 238277 | OPEN | 3/29/2022 0:00 |
| 238277 | OPEN | 3/30/2022 0:00 |
| 238277 | OPEN | 3/31/2022 0:00 |
| 238277 | OPEN | 4/1/2022 0:00 |
| 238277 | OPEN | 4/2/2022 0:00 |
| 238277 | OPEN | 4/3/2022 0:00 |
| 238277 | OPEN | 4/4/2022 0:00 |
| 238277 | OPEN | 4/5/2022 0:00 |
| 238277 | OPEN | 4/6/2022 0:00 |
| 238277 | OPEN | 4/7/2022 0:00 |
| 238277 | OPEN | 4/8/2022 0:00 |
| 238277 | OPEN | 4/9/2022 0:00 |
| 238277 | OPEN | 4/10/2022 0:00 |
Table 2 - Post History
| REQ_NUM | START_DT | END_DT |
| 238277 | 3/24/2022 0:00 | 4/3/2022 0:00 |
| 238277 | 5/3/2022 0:00 | 5/9/2022 0:00 |
| 238277 | 5/26/2022 0:00 | 5/27/2022 0:00 |
Basically by my logic, this requisition should be counted as posted from 3/23 - 4/3 and not counted again until 5/3
I created a measure that meets the logic, but it makes me aggregate the start_dt and end_dt to a min and max which is would show it posted on days when it actually wasn't.
Any clues or suggestions would be helpful as far as what function to look for.
