Measure is not evaluated over date attribute and returns blank

[Anonymous]

Hello,

I have run into an issue which I am not able to solve by myself. I suspect it might have to do something to do with filter contexts, but I might completely off. Here is the requirement:

My dataset has flight prices, and the relevant columns for this task are two date columns: the date of the flight departure [StartDate] and the day the price was quoted [InsertDate].

The business needs to know the price difference between any two given dates of insertion, over the different dates of departure. I managed the first half thanks to this post, and you can see the results here:

Note how the value is correct also for non-consecutive dates. The calculation for the average minimum price of the previous available date is as follows:

Average Minimum Price Previous = 
VAR _current =
    SELECTEDVALUE ( Flights[InsertDate] )
VAR _previous =
    CALCULATE (
        MAX ( Flights[InsertDate] );
        ALLSELECTED ( Flights[InsertDate] );
        KEEPFILTERS ( Flights[InsertDate] < _current )
    )
RETURN
    CALCULATE (
        [Average Minimum Price];
        Flights[InsertDate] = _previous;
        REMOVEFILTERS ( 'Calendar' )
    )

However, the issue appears when I try to evaluate these measure over my other date dimension, [StartDate]:

Now, all of the values for my previous price measure are blank, and I can't understand why. It happens with any other attribute I tried as well.

My final objective is something like this:

Where the each data point represents the price difference between the two dates selected in a slicer. I realise this would be far easier with a waterfall chart, but unfortunately the business is requesting this visualization as a line chart.

Please, could you explain how to get this to work, or guide me in the general direction?

Thank you very much in advance,

Alex

[Anonymous · Accepted solution]

I ended up finding a solution that might not be very performant, as it creates virtual tables, but that works:

Average Minimum Price Variation = 
VAR _c =
    MAX ( Flights[InsertDate] )
VAR _p =
    CALCULATE (
        MAX ( Flights[InsertDate] );
        KEEPFILTERS ( Flights[InsertDate] < _c )
    )
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE (
                Flights;
                [StartDate];
                "Avg Min"; CALCULATE ( [Average Minimum Price]; Flights[InsertDate] = _c );
                "Prev Avg Min"; CALCULATE ( [Average Minimum Price]; Flights[InsertDate] = _p )
            );
            "Diff"; [Avg Min] - [Prev Avg Min]
        );
        [Diff]
    )

I use SUMMARIZE to add the StartDate to the current context of the expression, and use my previous date as a CALCULATE filter. This generates a temporary table with both prices, current and previous, which is then expanded with ADDCOLUMNS to finally calculate the price difference between dates. Since dates are unique in the table generated by SUMMARIZE, I use AVERAGEX to iterate through it and get the final measure.

Anyway, if anyone could find a better implementation, I would be more than happy to read it. 

Thanks,

Alex