Merge based on date ranges

[Lost_Number]

I am fairly new to PowerBI and really can seem to find a specific or even related solution online to my problem.

The issue:

Table1 is a list of Sales persons with Start date / End date and target values

Person	Start	End	Target
SP1	10/01/2023	01/12/2023	10000
SP2	15/04/2023	19/05/2023	100
SP3	01/01/2023	31/08/2023	315

Table2 is a list of dates that starts from the List.Min(Start) to List.Max(End) of Table1.

I need to merge Table2 to Table1 but for every date (row level) in Table2 for each sales person within each start to end range.

the output would end up showing a row for every date in Table2 duplicated for each sales person between their respective Start and End date.

I have already played around with merge and creating additional join columns but I can't seem to get the correct output. I know this can also be done with Dax using crossjoin and filter but I have to build this into power query.

Thanks in advance

[lbendlin · Accepted solution]

I need to merge Table2 to Table1 

You don't need to do that. Merges are bad for your health.

Let the data model do the work for you. Worst case use GENERATESERIES and INTERSECT in DAX to solve this.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to 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.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Lost_Number]

Thank you for the info - sadly I went the merge route.