Forum Discussion
Complicated Pivot Table Issue With SUMIF like logic.
Alright since I am a new user I can't upload pics.
This is a sample set of data:
Funding is cumulative throughout the year and not particular to one month. So if it is 2022-03 Funding is 20 for MGMT and not 15. The larger data set has multiple Units for same dates in different rows as shown in a few examples in the sample set. Now in the pivot table, unfiltered the values for funding are correct since they are overall sums of funding for entire time frame 01-12. If I filter by date I only return the funding for that month instead of a summation of every month before that as well. So If I pick 2022-04 from the date slicer My funding in the pivot table should be MGMT funding: 20 Power Funding: 95 and Sales Funding: 90 but instead I get MGMT: 0 POWER: 20 SALES funding: 80. Is there any way to calculate this in power query, power pivot, etc in order to get this type of field.
| Unit | Date | Funding | $Sales | ||||
| MGMT | 2022-01 | 5 | 55 | ||||
| SALES | 2022-02 | 10 | 5 | ||||
| MGMT | 2022-03 | 15 | 302 | ROW LABELS | SUM SALES | SUM FUNDING | |
| POWER | 2022-04 | 20 | 439 | ||||
| MGMT | 2022-05 | 25 | 203 | MGMT | 10843 | 190 | |
| SALES | 2022-06 | 30 | 604 | POWER | 3960 | 210 | |
| MGMT | 2022-07 | 35 | 402 | SALES | 2365 | 280 | |
| SALES | 2022-08 | 40 | 405 | ||||
| MGMT | 2022-09 | 45 | 9532 | ||||
| SALES | 2022-10 | 50 | 349 | ||||
| POWER | 2022-11 | 55 | 2021 | ||||
| POWER | 2022-12 | 60 | 607 | ||||
| MGMT | 2022-01 | 65 | 349 | ||||
| SALES | 2022-02 | 70 | 768 | ||||
| POWER | 2022-03 | 75 | 893 | ||||
| SALES | 2022-04 | 80 | 234 |
Hello, I tried to do it with power query. Attached is the file... it could fail if it contains data for more than one year...
I think this should be easier with an excel formula maybe a sumifs or something to create the ytd column.
