Forum Discussion

linusb's avatar
linusb
Frequent Visitor
5 years ago
Solved

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

  • AlB's avatar
    AlB
    Community 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 

    • linusb's avatar
      linusb
      Frequent Visitor

      Thank you for the help. 

       

      // Linus