Forum Discussion
DAX - Crossjoin with Criteria
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 |
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 .
7 Replies
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 .
Appreciate it! I was trying =calculatetable(crossjoin(... and it wasn't working. Simpler is better!
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])))Thanks - I haven't played with GENERATE before but will check that out.
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)] ) )
