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 is not ignoring page filter

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

1 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.


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2

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.


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge 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!

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.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors