Rolling Cumulative Historic Date Filter

[lpriceFTW]

Hello,

I am creating a program status dashboard with daily transaction data.

I want to be able to filter the dashboard view in time slices such as "30" days, "60", "90", "180", "365", and "all".

I currently use a filter visual, linked to a small time slice table as such:

The above table is linked to a date table containing all unique dates. When a user selects a time slice in the filter visual, the time slice then filters the date table to only the unique dates in the time slice. The date table then filters my transaction table to return data to my other visuals on the report.

My problem, is that the filter is not returning cumulative historic data.

For example, when the user selects "90 days", only the data with dates falling between 60 and 90 days is returns. This is because my intermediate table only has one time slice value assigned per unique date. (see below).

in the above screenshot, you can see that 6/22/2025 will only ever be filtered if the "30 days" slice is selected. If the user selects "90 days", then it wont capture any dates with 30, 60, 180, etc.

Here is my table setup:

I did not anticipate the limits of my setup so now I am trying to rethink how I go about this.

My thoughts are to try and create some filter measure which affects all visuals on the report at once when the filter visual is changed. I'm just not sure how to approach this conceptually.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[MattAllington · Accepted solution]

The way I normally solve such problems is to use a time intelligence table that contains all the complete sets of dates for 30, 60, 90 etc. Doing this will create date duplicates, so you need to set it as many to 1 to the date table and turn on bidirectional cross filters. 
I always create such a table using union queries inside "new table" using DAX. You could do it other ways. 

The following is illustrative only

DateUnionTable =
VAR Last30Days =
    SELECTCOLUMNS(
        FILTER(
            'Calendar',
            'Calendar'[Date] >= TODAY() - 29 && 'Calendar'[Date] <= TODAY()
        ),
        "Date", 'Calendar'[Date],
        "Period", "Last 30 Days"
    )
VAR Last60Days =
    SELECTCOLUMNS(
        FILTER(
            'Calendar',
            'Calendar'[Date] >= TODAY() - 59 && 'Calendar'[Date] <= TODAY()
        ),
        "Date", 'Calendar'[Date],
        "Period", "Last 60 Days"
    )
VAR Last90Days =
    SELECTCOLUMNS(
        FILTER(
            'Calendar',
            'Calendar'[Date] >= TODAY() - 89 && 'Calendar'[Date] <= TODAY()
        ),
        "Date", 'Calendar'[Date],
        "Period", "Last 90 Days"
    )
VAR Combined =
    UNION(
        Last30Days,
        Last60Days,
        Last90Days
    )
RETURN
    Combined

[lpriceFTW]

Wonderful! This solved my issue perfectly. Thank you very much.