Struggling to create a calculation that uses dates from different tables

[MKMayers]

Hi,

I have a problem where my volume sold is duplicating. This is because some SKU's have 2 recipes - the recipe has changed slightly but it is the same product. We have dates for when these recipes are valid and what I am trying to do is pull back the details of the recipe that is valid at a given time along with the volume for that month.

I have created a measure for this - when the month is between the launch date and the discontinued date then "Yes" else "No". That appears to have worked. Then it's a simple case of filtering when Correct Recipe = "Yes". I get what I expect when I do this. 

However, the problem I have is as the recipe dates and Full Date (month of sale) are in separate tables I had to use the max() function in my measure: 

Correct Recipe = switch(true(), max(Financial_Data[Full Date]) >= max('OPOH NUTRITION'[Launch Month]) && MAX(Financial_Data[Full Date]) < MAX('OPOH NUTRITION'[Discontinued Month]), "Yes", "No")

I want a card with just the volume sold on it, but as my measure uses the max fucntion() it just takes the max launch date etc. overall and does not give me the figures I need. 

I have also tried to create a column/measure that says if the order month is between the launch and discontinued dates then "Volume" else 0 and then I was planning to use SUMX(). But again I'm struggling to references dates from different tables in the same calculation. 

Does anyone know how to get around this please?

Thanks

Michael

[Rupak_bi]

Hi, do this calculation in table itself. bring initial date and final date column in same table wheter volum column available based on SKU code and then calculate "Yes" "No" in colums and then you ma have a measure to filter "YES"

[Dangar332]

Hi, @MKMayers 

Try to create table refrence and use it inside of sumx()

try below   it might work

just adjust table and column name

result =
var a = 
sumx( 
   filter(
      summarizecolumn(
      'yourtablename'[sku code],'opoh nutrition'[launch month],
      'opoh nutrition'[discontinued month],'financial_data'[fulldate],
      'yourtablename'[volumn],"correct recipe",
          switch(true(),
                  max(Financial_Data[Full Date]) >= max('OPOH NUTRITION'[Launch Month]) && 
                  MAX(Financial_Data[Full Date]) < MAX('OPOH NUTRITION'[Discontinued Month]), 
              "Yes", "No"
                  )
                      ),
       ["correct recipe"]="yes"
           ),
   'yourtablename'[volumn]
    )

[MKMayers]

Hi @Dangar332 ,

Thanks for the idea. However, when I try this I get this error: 

SummarizeColumns() and AddMissingItems() may not be used in this context.

Do you know how to solve this?

Thanks

Michael 

[Dangar332]

hi, @MKMayers 

result =
var a = 
sumx( 
   filter(
      summarize('yourtablename',
      'yourtablename'[sku code],'opoh nutrition'[launch month],
      'opoh nutrition'[discontinued month],'financial_data'[fulldate],
      'yourtablename'[volumn],"correct recipe",
          switch(true(),
                  max(Financial_Data[Full Date]) >= max('OPOH NUTRITION'[Launch Month]) && 
                  MAX(Financial_Data[Full Date]) < MAX('OPOH NUTRITION'[Discontinued Month]), 
              "Yes", "No"
                  )
                      ),
       ["correct recipe"]="yes"
           ),
   'yourtablename'[volumn]
    )

try to use  summarize instaed of summarizecolumn

[MKMayers]

Thank you!