Rolling DistinctCount + Filters

[JCMT]

Hi All, 

My dataset has a calendar table and a table containing records with various properties that are linked by date to the calendar table, I've setup a measure to output a rolling total count of unique record IDs for the past 7 days as per below: 

Unique Record IDs 7D Count = 
CALCULATE(
    SUMX(
        VALUES(Calendar[Date]), DISTINCTCOUNT(Records[ID]),
        FILTER(
            ALLSELECTED(Calendar),Calendar[Date] >= (max(Calendar[Date])-6) && Calendar[Date] <= max(Calendar[Date])
            )
            )

 This measure works perfectly if the data is unfiltered, however applying a filter to one of the fields in the Record table restricts the available date range doesn't give me the result I'm after. 

Below is an example of the output of the Rolling Count after filtering 

Date		Unique Record IDs	Rolling Count (7D)
25/08/2024		0
26/08/2024		0
27/08/2024		3	3
28/08/2024		5	8
29/08/2024		10	18
30/08/2024		6	24
31/08/2024		0
1/09/2024		0
2/09/2024		0
3/09/2024		0
4/09/2024		0
5/09/2024		0
6/09/2024		0
7/09/2024		0

And here's the desired output 

Date		Unique Record IDs	Rolling Count (7D)
25/08/2024		0	0
26/08/2024		0	0
27/08/2024		3	3
28/08/2024		5	8
29/08/2024		10	18
30/08/2024		6	24
31/08/2024		0	24
1/09/2024		0	24
2/09/2024		0	24
3/09/2024		0	21
4/09/2024		0	16
5/09/2024		0	6
6/09/2024		0	0
7/09/2024		0	0

I was under the impression that ALLSELECTED might override the impact of the filter applied to the record table but that seems to be an incorrect assumption on my part. 

Found this post which seemed to be an almost identical situation however I've had no luck adapting the solution to get the desired output. Any suggestions / assistance would be much appreciated.

Edit: Sorry - table formatting bad. 

[JCMT · Accepted solution]

Still haven't identified exactly what the issue was but it seems it was something in the source dataset because when I started from scratch with a fresh copy of the data and re created the measures it all behaved as expected. 

Ultimately I wanted a rolling average, here are the final measures: 

Unique Record IDs = COALESCE(DISTINCTCOUNT(Records[ID]), 0)


Unique Record IDs Rolling 7D Average =
DIVIDE(
    CALCULATE(
        SUMX(
            VALUES('Calendar'[Date]), [Unique Record IDs]
            ),
            REMOVEFILTERS('Calendar'[Date]),
            'Calendar'[Date] >= (max('Calendar'[Date])-6) && 'Calendar'[Date] <= max('Calendar'[Date])
            ),
        7
)

[SamWiseOwl]

Hi @JCMT 

Three things to note:
AllSelected removes internal filters, so the filters caused by the date row inside of the visual.

All removes all filters on the given table/column both internal (visual) and external (slicer).

This could be part of the misunderstanding.

Secondly:

Calculate lets you change, replace or remove filters.

Filter keeps the existing filters and adds to them.

So this could also be a cause of your issue.

Finally applying a filter to one of the fields in the Record table

Removing/adding filters to the calendar table will affect the Record table.

However if the filter is on some other column for example RecordType this wouldn't be removed by the AllSelected here.

You could use something like AllExcept(Record, Calendar[Date]) to remove all filters on the Record table but keep the filters on the Calendar table.

What are the filters you are adding and which do you want to keep?

[JCMT]

Thank you very much for your response..

Finally applying a filter to one of the fields in the Record table

Removing/adding filters to the calendar table will affect the Record table.

However if the filter is on some other column for example RecordType this wouldn't be removed by the AllSelected here.

You could use something like AllExcept(Record, Calendar[Date]) to remove all filters on the Record table but keep the filters on the Calendar table.

What are the filters you are adding and which do you want to keep?

---

I want to keep the filter on the Record table (eg: RecordType) and remove any implicit filtering on the Calendar table. 

I can't paste raw data but can generate a mock up if that's needed to help further.