For each opportunity generate rows of dates

[nseward]

I currently have a table of opportunities with start/end dates and estimated revenue. 

I'm trying to generate a series of revenues for it while still retaining the ability to filter by opportunity. 

Input Table #1

Opportunity	Start Date	End Date	Estimated Revenue	Monthly Revenue
A	3/1/2024	9/1/2024	$100,000	$16,667
B	4/1/2024	7/1/2024	$125,000	$41,667

Input Table #2

Dates

4/1/2024

5/1/2024

6/1/2024

7/1/2024

8/1/2024

9/1/2024

Desired Output Table

Opportunity	Date	Revenue
A	4/1/2024	$16,667
A	5/1/2024	$16,667
A	6/1/2024	$16,667
A	7/1/2024	$16,667
A	8/1/2024	$16,667
A	9/1/2024	$16,667
B	5/1/2024	$41,667
B	6/1/2024	$41,667
B	7/1/2024	$41,667

[Daniel29195 · Accepted solution]

@nseward 

output

caluclated table : 

Table 2 = 



var ds = 
ALLNOBLANKROW(tbl1)

var ds1 = 
SELECTCOLUMNS(
GENERATE(
    ds,
    var v1 = tbl1[Start Date]
    var v2 = tbl1[End Date]
    RETURN
    CALENDAR(v1,v2)
),
"opportunity", tbl1[Opportunity],
"start date" , tbl1[Start Date],
"end date", tbl1[End Date],
"estimated revenue",tbl1[Estimated Revenue],
"monthly revenue", tbl1[Monthly Revenue],
"Date", [Date] & ""

)

var tb2 = 
SELECTCOLUMNS(
    tbl2,
    "Date",tbl2[Dates] & ""
)

var ds3 = 
FILTER(
NATURALINNERJOIN(
    ds1,
    tb2
),
datevalue([Date]) <> datevalue([start date]))



return ds3

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠