CALCULATE with FILTER/ALL not working for all sales reps

[I_Like_Power_BI]

I have the following code to pull in sales data from the previous year for each sales rep in my organization:

Last Year SDS Rev 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[SDS Revenue]),
        FILTER(
            ALL(FiscalCalendar),
            FiscalCalendar[ACTUAL_DATE] >= _firstDayOfQuarter && FiscalCalendar[ACTUAL_DATE] <= _endDateToUse
        )
    )

    return _deployLY_QTD

I have a table visual to confirm that it's working, and it is for about half of my sales reps.  Blanks are showing up for the other half.

I have confirmed that the reps with blanks do have sales data from last year in the actual table, but for some reason it's not pulling in.

Any help here is greatly appreciated!  I've been trying to figure this measure out for about 5 days now!

[Anonymous]

Without seeing more, what if you made the following changes in your last variable?

Last Year SDS Rev 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[SDS Revenue] ),
        FILTER (
            ALL ( FiscalCalendar ),
            MAX ( FiscalCalendar[ACTUAL_DATE] ) >= _firstDayOfQuarter
                && MAX ( FiscalCalendar[ACTUAL_DATE] ) <= _endDateToUse
        )
    )
RETURN
    _deployLY_QTD

[I_Like_Power_BI · Accepted solution]

Thanks for your response.  I found the issue was with my relationships between tables.

Thanks again!