CALCULATE with FILTER/ALL is not ignoring page filter

[I_Like_Power_BI]

I am trying to get Sales data from the same period last year (quarter to date).  I have variables that carry the correct start and end date for my date range.  Here is the logic in my Measure:

Last Year Deploy QTD =


    var _firstDayOfQuarter = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Last Year Same Quarter Start"))

    var _today = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Current Date"))

    var _sameDayLastYear = date(year(_today)-1,month(_today),day(_today))

    var _lastDayOfQuarter = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Last Year Same Quarter End"))

    var _endDateToUse = if(_sameDayLastYear > _lastDayOfQuarter, _lastDayOfQuarter, _sameDayLastYear)


    var _deployLY_QTD = CALCULATE(

        sum(DemandReport[SCP_Deploy_Rev]),

        FILTER(

            ALL(DemandReport),

            DemandReport[Order_Date] >= _firstDayOfQuarter && DemandReport[Order_Date] <= _endDateToUse

        )

    )


    return _deployLY_QTD

I’ve also tried taking off the "Filter" function:

    var _deployLY_QTD = CALCULATE(

        sum(DemandReport[SCP_Deploy_Rev]),

        DemandReport[Order_Date] >= _firstDayOfQuarter && DemandReport[Order_Date] <= _endDateToUse

    )

And I’ve tried many other things.

Here’s the thing: these DAX expressions work.  But, they only work if my filter is set to a year ago.  This means the “ALL” command isn’t ignoring the filters I have set elsewhere on the page.

Can anyone help?  I'm new to Power BI, so I might be missing something obvious.

Thanks

[MattAllington · Accepted solution]

You shouldn’t try to do time intelligence in a single flat table. Create a calendar table and join it to your current date column. Move all references to date and filtering to your calendar table.

[I_Like_Power_BI]

That did it!  I'll try to find some reading around why that worked, as I don't quite understand it.

I have another issue now with filtering reps, but I suppose I'll post that on another thread.

Thanks again!