Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
I_Like_Power_BI
Helper II
Helper II

CALCULATE with FILTER/ALL not working for all sales reps

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!

1 ACCEPTED SOLUTION

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

 

Thanks again!

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

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

 

Thanks again!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors