How to calculate running total + total remaining in a pivot?

This is what Power Pivot is great at.

First of all, I would add a real date column to your source data, and add a date table to your workbook - then you can use the date / time functions of DAX.

It's straightforward to build the measures you want:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Then simply build your pivots as you want them.

See attached workbook for a worked example.

Glad it helped, and happy to have introduced you to Power Pivot!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.

Sure thing! Thanks again. I'm still trying to replicate it with my set of data. I'll get back to you ASAP :-)

By the way, there's something I don't fully understand. In your example, the Dates table shows values from 2015 to 2019, which is great. And your pivot only shows data for 2017 and 2018, which is right.

But in my new set of data I get results in the pivot table for all years in Dates table, although I have only values for 2017 and 2018. Any ideas? Thanks!

Your date table should have ALL dates in it, between the earliest and latest dates you need, so that the DAX date functions work correctly. You'll see that the date table in the workbook I shared with you is generated by a function - you can easily generate a different date table, covering a different range of dates, and using different locale settings.

Don't forget to define the date table as the "date" table in your power pivot model, and to create a relationship between the date field of the date table, and the date field of your fact table(s)