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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.