DAX - Crossjoin with Criteria

[danb]

I have been asking Copilot but without success so coming to the real experts! 

I have two tables, one called 'BrandForecast' and the other 'Running Calendar'. I am trying to figure out how to do a crossjoin in dax with a catch. I don't want each campaign to be repeated for all of the weeks in the Running Calendar. Instead i only want the crossjoin to take place when the [Week Starting (Monday Start) is greater than or equal to the [Promotion Start Date] and less than or equal to the [Promotion End Date]. I have included working data below:

The end result I am trying to get to is something like this: 

Week Starting (Monday Start)	Campaign	Promotion Start Date	Promotion End Date	Plate	Baseline	Forecast
11/3/2025	Monthly 3	11/3/2025	11/30/2025	Ramen	27	11
11/3/2025	Monthly 3	11/3/2025	11/30/2025	Soba	30	12
11/3/2025	Monthly 3	11/3/2025	11/30/2025	Shrimp Faces	29	44
11/10/2025	Monthly 3	11/3/2025	11/30/2025	Ramen	27	11
11/10/2025	Monthly 3	11/3/2025	11/30/2025	Soba	30	12
11/10/2025	Monthly 3	11/3/2025	11/30/2025	Shrimp Faces	29	44
11/17/2025	Monthly 3	11/3/2025	11/30/2025	Ramen	27	11
11/17/2025	Monthly 3	11/3/2025	11/30/2025	Soba	30	12
11/17/2025	Monthly 3	11/3/2025	11/30/2025	Shrimp Faces	29	44
11/24/2025	Monthly 3	11/3/2025	11/30/2025	Ramen	27	11
11/24/2025	Monthly 3	11/3/2025	11/30/2025	Soba	30	12
11/24/2025	Monthly 3	11/3/2025	11/30/2025	Shrimp Faces	29	44

'BrandForecast'

Campaign	Promotion Start Date	Promotion End Date	Plate	Baseline	Forecast
Core 1	12/30/2024	6/29/2025	Burger w/Avocado	10	15
Core 1	12/30/2024	6/29/2025	Steak & Eggs	6	11
Core 1	12/30/2024	6/29/2025	Mayo Sandwich	3	9
Core 2	6/30/2025	11/30/2025	Sausage Pizza	5	14
Core 2	6/30/2025	11/30/2025	Stinky Cheese Plate	3	5
Core 2	6/30/2025	11/30/2025	Bacon Burger	5	15
Monthly 1	10/3/2025	10/30/2025	Fish Sandwich	28	9
Monthly 1	10/3/2025	10/30/2025	Shrimp Plate	41	16
Monthly 1	10/3/2025	10/30/2025	Nacho Fries	29	27
Monthly 2	6/30/2025	7/27/2025	Carne Asada	38	31
Monthly 2	6/30/2025	7/27/2025	Pork Carnitas	7	49
Monthly 2	6/30/2025	7/27/2025	Tacos	47	30
Monthly 3	11/3/2025	11/30/2025	Ramen	27	11
Monthly 3	11/3/2025	11/30/2025	Soba	30	12
Monthly 3	11/3/2025	11/30/2025	Shrimp Faces	29	44
Promo	6/2/2025	6/29/2025	Ice Cream	14	22
Promo	6/2/2025	6/29/2025	Melted Butter	16	30
Promo	6/2/2025	6/29/2025	Lettuce & Raisens	48	23

'Running Calendar'

Week Starting (Monday Start)

11/4/2024

11/11/2024

11/18/2024

11/25/2024

12/2/2024

12/9/2024

12/16/2024

12/23/2024

12/30/2024

1/6/2025

1/13/2025

1/20/2025

1/27/2025

2/3/2025

2/10/2025

2/17/2025

2/24/2025

3/3/2025

3/10/2025

3/17/2025

3/24/2025

3/31/2025

4/7/2025

4/14/2025

4/21/2025

4/28/2025

5/5/2025

5/12/2025

5/19/2025

5/26/2025

6/2/2025

6/9/2025

6/16/2025

6/23/2025

6/30/2025

7/7/2025

7/14/2025

7/21/2025

7/28/2025

8/4/2025

8/11/2025

8/18/2025

8/25/2025

9/1/2025

9/8/2025

9/15/2025

9/22/2025

9/29/2025

10/6/2025

10/13/2025

10/20/2025

10/27/2025

11/3/2025

11/10/2025

11/17/2025

11/24/2025

12/1/2025

12/8/2025

12/15/2025

12/22/2025

12/29/2025

1/5/2026

1/12/2026

1/19/2026

1/26/2026

2/2/2026

2/9/2026

2/16/2026

2/23/2026

3/2/2026

3/9/2026

3/16/2026

3/23/2026

[danextian]

Try this:

CrossJoinedTable = 
GENERATE (
    BrandForecast,
    VAR StartDate = BrandForecast[Promotion Start Date]
    VAR EndDate = BrandForecast[Promotion End Date]
    RETURN
        SELECTCOLUMNS (
            FILTER (
                RunningCalendar,
                RunningCalendar[Week Starting (Monday Start)] >= StartDate &&
                RunningCalendar[Week Starting (Monday Start)] <= EndDate
            ),
            "Week Starting (Monday Start)", RunningCalendar[Week Starting (Monday Start)]
        )
)

[danb]

I haven't messed around with GENERATE, what are the benefits of that function from a performance perspective versus crossjoin? 

[lbendlin]

Go ahead and compare the performance.  You would have to look at the query plan to see if there is even a difference.

[lbendlin]

There is no magic to it.

Result = FILTER(CROSSJOIN(BrandForecast,'Running Calendar'),[Week Starting] IN CALENDAR([Promotion Start Date],[Promotion End Date]))

No idea if there is any performance benefit using GENERATE instead. Probably not.

Result2 = GENERATE(BrandForecast,filter('Running Calendar',[Week Starting] in CALENDAR([Promotion Start Date],[Promotion End Date])))

[danb]

Thanks - I haven't played with GENERATE before but will check that out. 

[Daniel29195 · Accepted solution]

@danb 

create a calculatedtable as below : 

filter ( 

crossjoin ( table1 , table2 ) , 

 [Week Starting (Monday Start) >= [Promotion Start Date]  && [Week Starting (Monday Start)  <= [Promotion End Date]

)

hope this helps . 

[danb]

Appreciate it! I was trying =calculatetable(crossjoin(... and it wasn't working. Simpler is better!