Forum Discussion
Power pivot - how to count data after a category change
Hi everybody, i'm using power pivot for builing a report, these are my data:
Project table:
| Project
| Category | Project value | Date |
A | 1 | 1000 | 01/01/2023 |
A | 1 | -1000 | 01/06/2023 |
A | 2 | 1000 | 01/06/2023 |
When a project changes its category, his value is reduced of the entire amount, then i have another row with that amount.
In the second table i have the payments assigned to that project. es:
Payment table:
Project | payment amount | date |
A | 160 | 30/01/2023 |
A | 160 | 30/06/2023 |
What I want to do is create a filtered pivot table with a timeline that counts the project in either category 1 or 2 depending on the current date (before or after 01/06/2023).
Furthermore, it should allocate the payment amount of €160 to category 1 if it's before 01/06/2023, and to category 2 if it's after this date.
Thanks!
