Hi there.
I have a dataset which contains the following.
Unique ID | File Owner | Start Date | Finish Date | Duration | Demanded $ | Received $ | Recovery Rate
This data changes daily and contains many thousands of records.
I have separate pivot tables recording the:
- start dates (values) by owner (columns) by month (rows); and
- finish dates (values) by owner (columns) by month (rows).
I want to report data which shows the difference between the two - i.e. subtract the finished count in a month from those started in a month, by owner.
Pulling both into a single pivot table doesn't work because the data all sorts based on the start date - the finish data does not use the "finish date" like the individual pivot table.
Even if I could get this to work, using a calculated field subtracting "Count" of Start Date - "Count" of Finish Date also does not seem to work.
Is there a way to report this data automatically? I'm told its possible using power query but not sure how to do this.
A mocked up example of my tables can be found on link from Onedrive at 1drv.ms/x/s!AqLilZzOCuC8k3-U8ca2TI4c6Fau or as attached.
Bookmarks