Forum Discussion
Help understand why measure doesn't work
Hello, I have problem with a measure and I don't understand why is isn't working.
I want to analyze changes to the purchase price over time. I have four tables in the file (dates, itemdata, demandperitem and price_overtime).
Since I only update the price_overtime (the table with all price changes) when the price has been changed, I get blank values for the months, where the value is unchanged.
See second table in https://imgur.com/a/vNGqxaT
I.e September 2020 is blank -> the price should be 5.
I have created a measure called var_Date V2, that is looking for the latest date in the price_overtime-table.
var_Date v2 =
Var
// Maxdatum i givet urval
var_MaxDate = Max(Dates[Date])
Var
var_Date =
TOPN(
1,
FILTER(
ALL('Price Development Overtime'[Date]),
'Price Development Overtime'[Date] < var_MaxDate),
'Price Development Overtime'[Date],
DESC)
Var
var_Selected_ItemNo =
SELECTEDVALUE(Itemdata[ItemNo])
Return
var_Date
//DATEVALUE("2020-05-17")
It seems to work correctly. If I select October 2020, var_date v2 will return the date of August 2020 (see second table in https://imgur.com/a/vNGqxaT Column var_date v2)
I have created a second measure "Test - Use variable Date".
Test - Use variable Date =
CALCULATE(
SUM('Price Development Overtime'[Price]),
FILTER(
All('Price Development Overtime'),
'Price Development Overtime'[Date] = [var_Date v2] // Use variable Date
)
)
This measure doesn't work. It will return Blank result. See first image in https://imgur.com/a/vNGqxaT. (Bottom table in the picture).
If I create a measure using hard values called "Test - Fixed values" for date 2020-05-17.
Test - Fixed date =
CALCULATE(
SUM('Price Development Overtime'[Price]),
FILTER(
All('Price Development Overtime'),
'Price Development Overtime'[Date] = DATEVALUE("2020-05-17") // Use Fixed value
)
)
This measure will return the correct value (Purchase Price = 1).
I don't understand why the measaure Test with the var_Date is Blank?
Or is there a better way to achieve what I want?
See link for the pbix-file: https://drive.google.com/file/d/1l4h3cE_Czlda30XBwQY_8aFKyExF4Qt-/view?usp=sharing
BR Linus
Hi linusb
I haven't looked in detail, but I'm pretty sure it has to do with context transition. Have you considered its effects when invoking [var_Date v2] from within the FILTER( ) operation? Try this:
Test - Use variable Date V2 = VAR aux_ = [var_Date v2] RETURN CALCULATE( SUM('Price Development Overtime'[Price]), FILTER( All('Price Development Overtime'), 'Price Development Overtime'[Date] = aux_ ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
2 Replies
- AlBCommunity Champion
Hi linusb
I haven't looked in detail, but I'm pretty sure it has to do with context transition. Have you considered its effects when invoking [var_Date v2] from within the FILTER( ) operation? Try this:
Test - Use variable Date V2 = VAR aux_ = [var_Date v2] RETURN CALCULATE( SUM('Price Development Overtime'[Price]), FILTER( All('Price Development Overtime'), 'Price Development Overtime'[Date] = aux_ ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- linusbFrequent Visitor
Thank you for the help.
// Linus