Using different columns in Slicer and PARALLELPERIOD breaks my measure

Question

I have a table that contains data for 13 prior month ends as well as the most recent business day. The majority of my reports only look at a single period at a time so I have a slicer on each report to allow the user to choose the period they want to look at, which is generally the most recent business day. Whenever the data gets refreshed I have to manually go to each slicer that had the most recent business day selected and choose the new most recent business day (unfortunately my organization hasn't updated to the version that allows the slicers to be synced). As we move towards production and scheduled refreshes, this will be a nuisance so I added a second column called REPORTING_DATE, which is equal to the original DATA_DATE field except the most recent date is replaced with 'Most Recent' so any slicers with that selected can maintain their selection after a refresh.

This is a simplified example of my data:

DATA_DATE   REPORTING_DATE  ACCOUNT_NO
7/10/2018   Most Recent     1001
7/10/2018   Most Recent     1002
7/10/2018   Most Recent     1003
7/10/2018   Most Recent     1004
7/10/2018   Most Recent     1005
7/10/2018   Most Recent     1006
7/10/2018   Most Recent     1007
6/30/2018   6/30/2018       1001
6/30/2018   6/30/2018       1002
6/30/2018   6/30/2018       1003
6/30/2018   6/30/2018       1004
6/30/2018   6/30/2018       1005
6/30/2018   6/30/2018       1006
5/31/2018   5/31/2018       1001
5/31/2018   5/31/2018       1002
5/31/2018   5/31/2018       1003
5/31/2018   5/31/2018       1004

My issue is that when I change my slicer to use DATA_DATE instead of REPORTING_DATE it breaks my measure that I use to calculate the change in counts for each period.

Change in Count (Month) = DISTINCTCOUNT(MyData[ACCOUNT_NO])-CALCULATE(DISTINCTCOUNT(MyData[ACOUNT_NO]),PARALLELPERIOD(MyData[DATA_DATE],-1,MONTH))

When my slicer has DATA_DATE = 7/10/2018 the measure correctly returns 1 (count of 7 for July 10 minus a count of 6 for June 30). When I use a slicer with REPORTING_DATE = Most Recent I get 7 because DISTINCTCOUNT(MyData[ACCOUNT_NO]) returns 7, which is correct, but CALCULATE(DISTINCTCOUNT(MyData[ACOUNT_NO]),PARALLELPERIOD(MyData[DATA_DATE],-1,MONTH)) returns (Blank). It looks like PARALLELPERIOD(MyData[DATA_DATE],-1,MONTH) returns the same value 6/30/2018 regardless of the slicer being used so I'm stumped as to the issue.

Alexis Olson Alexis Olson · Accepted Answer · 2018-07-11T18:42:53

The reason this does not work is that the PARALLELPERIOD filter in your CALCULATE is only replacing the context filter for the [DATA_DATE] column but still has the slicer filtering in effect since that is on a different column. If you select Most Recent on the [REPORTING_DATE] slicer, your measure will try to find the distinct count where [REPORTING_DATE] is Most Recent and also [DATA_DATE] is in the previous month. Since no such rows exist, it returns a blank.

To fix this, you can tell the measure to ignore the filtering directly from the [REPORTING_DATE] slicer and only use the filtering on the [DATA_DATE] column (which gets indirectly filters from the slicer).

Change in Count (Month) =
    DISTINCTCOUNT(MyData[ACCOUNT_NO]) - 
    CALCULATE(
        DISTINCTCOUNT(MyData[ACOUNT_NO]),
        ALL(MyData[REPORTING_DATE]),
        PARALLELPERIOD(MyData[DATA_DATE], -1, MONTH))

The reason that this works with the slicer on [DATA_DATE] is that the PARALLELPERIOD filter replaces the slicer filtering for that column. When you slice on [REPORTING_DATE], the slicer filtering does not get replaced since you aren't referring to that column inside a CALCULATE filter argument.

If this still doesn't make sense, I recommend some reading on how the CALCULATE function works. There's an entire chapter devoted to it in The Definitive Guide to DAX and there's a handful of websites/blogs that have some decent reading too.

Using different columns in Slicer and PARALLELPERIOD breaks my measure

1 Answers