Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MKMayers
Frequent Visitor

Struggling to create a calculation that uses dates from different tables

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. 

 

MKMayers_0-1698668658308.png

 

 

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

5 REPLIES 5
Rupak_bi
Super User
Super User

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"



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Dangar332
Super User
Super User

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]
    )



 

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 

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

Thank you! 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.