Forum Discussion
lpriceFTW
1 year agoHelper II
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...
- 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
Ashish_Mathur
1 year agoSuper User
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.