Forum Discussion

lpriceFTW's avatar
lpriceFTW
Helper II
1 year ago
Solved

Rolling Cumulative Historic Date Filter

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...
  • MattAllington's avatar
    1 year ago

    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