Again I seem to be struggling with ALL and it not ignoring the filter as I would expect.
My scenario (based on some mock up data) is that we have sales for 2019 Jan through to July, however we only have Targets from April onwards.
I have a tab on my report, Sales vs Targets, which details sales performance against targets and for most of the tables, cards etc we only want to compare sales for where we have targets. I therefore want to hard code this page to ignore all sales pre April 2019. However, the exception being that we do have a bar chart which shows sales by month and for this, we would like to show sales for the whole year - Jan to July.
I have created a column in my Dates table as such:
Is Prior to 1st April 2019 = IF(Dates[Date] < DATE(2019,4,1), TRUE(), FALSE())
I have added this column to my Filters On This Page and set it to FALSE, thus restricting all the data on the report to April 2019 on.
I have then created a special Sales measure for use in the bar chart:
Sales for Chart =
CALCULATE(SALES[Sales], ALL(Dates[Is Prior to 1st April 2019]))
However, this doesn’t work as I would expect and the chart is still restricted to April, May and June, when I want it to show Jan to July.
What am I missing here! Mock up example is attached:
Sales vs Target - ALL query.pbix (121.0 KB)