Forum Discussion
Solved
Date filter in pivot tables
Hi, We have a scenario where we want to find the customer balance by a given date. Such balances can be based on a table similar to the following table: CustomerNo Voucher Date Amount ...
- 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
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
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