Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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"
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!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |