Connecting two tables - Lookupvalue with multiple conditions and Between two values

[Anonymous]

Hi All,

I'm trying to connect the two below tables. 

Below I've provided info about the tables, as well as some current attempts that aren't working. 

Any advice would be much appreciated. Thank you. 

Table info 

The All_promo table has a distinct row for each sku, store and range of the promo weeks. For example: 

• First row sku 2991316 at store 4 was on promo from 2021-week  9 to 2021-week 21 

The aggregate table has a distinct row for each sku, store and fiscal_year week. For example:

• Below, is the same sku as the first table. 
  • The last 3 rows would fall between the date ranges identified above of week 9 to week 21 of 2021

Current strategy to connect the two tables

• If I create a column on my aggregate table that looks up from the 'All_promo' table the 'fiscal_year_week' start date for any: 
  • 'fiscal_year_week' that falls between the 'fiscal_year_week_start' and 'fiscal_year_week_end' range on the 'All_promo' table
    
    • And also has the same sku_no and store_no
• I could then create a concatenated column of 'fiscal_year_week_start_sku_store'  on both tables, and connect on that. 

Other info

• The 'All_promo' table is distinct at the sku, store and fiscal_year_week_start to fiscal_year_week_end level. 
• The 'Agg' table has fiscal_year_weeks for these skus and stores that fall outside of the dates they were on promo

Current attempts at DAX to bring in the fiscal_year_week_start - so I can concatenate and connect. 

• This one only worked for the week of the start - not days between the start and end. 

fw_start = LOOKUPVALUE(All_promo[fiscal_year_week_start], All_promo[SKU_Number], Agg[sku_no], All_promo[Store_ID], Agg[store_no], All_promo[fiscal_year_week_start], Agg[fiscal_year_week])

• This one I tried to look up the closest valufiscal_year_week_start date from the  that was previous to the fiscal_year_week for row in the Agg table, but it would also provide a closest start value prior, even when it fell outside the upper bound of the end of the promo. 

 closest_start = CALCULATE( MAX(  VALUES( DISTINCT( All_promo[fiscal_year_week_start]))   , FILTER( CALCULATETABLE(  VALUES( DISTINCT( All_promo[fiscal_year_week_start]))  ), DISTINCT( All_promo[fiscal_year_week_start])<= Agg[fiscal_year_week]))

[PaulDBrown]

You should create dimension tables with unique values covering the range of values for fields (non value fields) common to both tables. You then create one -to-many relationships between each dimension field and the corresponding field in each table. You then use the fields from these dimension tables in you visuals, slicers, meaaures, filters...

Dax is much more simple with a this type of model.