PowerBI query doesn't work with date filter

Question

I have this DAX:

Earned Daily =
VAR d = SELECTEDVALUE ( 'P6 Update'[Date] )
RETURN
IF (
    'Data for Pivot'[Date] <= d
        && 'Data for Pivot'[Act Rem] = "Actual Units"
        && 'Data for Pivot'[Type] = "Current",
    'Data for Pivot'[Value],
    0
)

'P6 Update'[Date] is attached to a Slicer (single select date dropdown).

I see that the date is being correctly set but when I try to use the date to filter it doesn't work.

If I do this:

Earned Daily =
IF (
    'Data for Pivot'[Date] <= DATE ( 2018, 4, 19 )
        && 'Data for Pivot'[Act Rem] = "Actual Units"
        && 'Data for Pivot'[Type] = "Current",
    'Data for Pivot'[Value],
    0
)

The data is filtered correctly.

What's the problem with the original code?

#######

Note: P6 Update table is created with this code:

P6 Update = CALENDAR(MIN('Data for Pivot'[Date]), MAX('Data for Pivot'[Date]))

Answer 1

Your DAX formula appears to be a calculated column rather than a measure. Since calculated columns are only evaluated once each time the table is loaded, they cannot be responsive to slicers or dynamic filtering of any sort.

The solution is to write a measure to produce the values you want instead. It would look something like this:

Earned Daily =
VAR d = SELECTEDVALUE ( 'P6 Update'[Date] )
RETURN
CALCULATE (
    SUM ( 'Data for Pivot'[Value] ),
    'Data for Pivot'[Date] <= d,
    'Data for Pivot'[Act Rem] = "Actual Units",
    'Data for Pivot'[Type] = "Current"
)

You might need to make adjustments depending on what context you are trying to use this measure in, but this is the basic approach.