Forum Discussion
Date filter in pivot tables
- Hi both of you
Thank you for the input! I did not end up using it, but might for other scenarios.
I found that the best solution was to insert the timeline filter in the pivot table. Despite the timeline filter to be a bit chunky to work with, I figured that it created a relationship between the data and the selections in the pivot table to create the end balances per customer.
When the timeline filter it added is was able to hover over the date column (in the PivotTable Fields) and then filter on date without actually adding it as a filter or a value in the pivot table, although it appears to interact with the timeline filter and then manipulate the query in that way.
Thank you for the effort
Best,
Jonas
See the attached. I use the relatively new Dynamic Array functions UNIQUE and FILTER in this.
You can find these functions explained on this YouTube video. https://www.youtube.com/watch?v=9I9DtFOVPIg
Basically this combination allows you to "create" your own cross-tabulated table, similar to the result that a Pivot Table creates, but the FILTER function can be written to work with such things as variable ranges of dates.
- Hi
Thank you for the reply! It appears to be working, although the date in the cube has to be manually transformed to a date type.
Although there are no opportunities to make this operation in the pivot table instead of having to make a pivot table with all data and then apply the functions? As this may be equally cumbersome as using the timeline filter
Best
JonasThank you for the reply! It appears to be working, although the date in the cube has to be manually transformed to a date type.
I'm not sure why it's a problem to treat a date as a date type, rather than as a text string that looks like a date but is really a text string. Yes, there may be a need to convert it, if the data are coming from another source, but it just makes sense. Doesn't it?!
Although there are no opportunities to make this operation in the pivot table instead of having to make a pivot table with all data and then apply the functions? As this may be equally cumbersome as using the timeline filterAs I said in my original post, I've found it to generally be the case that I can use UNIQUE and FILTER and obviate the need for the Pivot Table altogether. FILTER enables the creating of very sophisticated criteria for which rows of data get summarized, which is what you're trying to do.
You may do that if work with data model. Creating PivotTable add data to data model, within Power Pivot create Calendar table and make relationships with source table
Add DAX measure as
Balance:=VAR maxDate=MAX('Calendar'[Date]) RETURN CALCULATE(SUM([Amount]), 'Calendar'[Date] <= maxDate)and use it to show balance
- Hi both of you
Thank you for the input! I did not end up using it, but might for other scenarios.
I found that the best solution was to insert the timeline filter in the pivot table. Despite the timeline filter to be a bit chunky to work with, I figured that it created a relationship between the data and the selections in the pivot table to create the end balances per customer.
When the timeline filter it added is was able to hover over the date column (in the PivotTable Fields) and then filter on date without actually adding it as a filter or a value in the pivot table, although it appears to interact with the timeline filter and then manipulate the query in that way.
Thank you for the effort
Best,
Jonas
