Show Balance outstanding everyday even if data for everyday is not available


In this simple 3 column dataset, there are 2 accounts - Konto 1 and Konto 2.  Each account has a balance outstanding as on a certain date.  However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020.

The objective is show the balance outstanding every day.  For days which are absent from the dataset, the balance outstanding should be the balance as on the previous day.  So for the Konto 1 account, on January 4-5, the balance should be 400 and on January 9-10, it should be 250.  The same logic applies for the Konto 2 account as well.  The expected result should be

I have solved this problem using Data > Get & Transform and PowerPivot.  You may download my solution workbook from here.

Here's another related question.  Given this 3 column dataset, the objective is to determine the total of the "PDV Combined Total" column for the past "5 billing days" from today - the past 5 working days have to be determined by looking at the "IsABillingDay" column.  So if today is December 4, 2020, then the past 5 business days would be November 30, 2020 to December 4, 2020.  The result should be 14,95,937.  However, if today was December 2, 2020, then the past 5 business days would be November 24, 2020 to December 2, 2020.  The result would be 14,33,545.  You may download the solution in a PBI file from here.  So while this question has been solved using the DAX formula language in PowerBI Desktop, since the same formula language exists in MS Excel as well, this result can be obtained in MS Excel as well.

Leave a Comment

Your email address will not be published.