Forum Discussion
Rolling Cumulative Historic Date Filter
- 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
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
- lpriceFTW1 year ago
Helper II
Wonderful! This solved my issue perfectly. Thank you very much.