cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## For each opportunity generate rows of dates

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
1 ACCEPTED SOLUTION
Super User

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! 🤠

Super User

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! 🤠

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors