Rolling forecast(?) question

[Anonymous]

Hello everyone,

I have been trying to solve this problem for a few days now and its driving me crazy since it seems so simple to solve in excel but not in BI

So I have 3 tables:

Disbursements

Manager	Company	Date	Disbursement
Tarlock	Microsoft	5-Jan-21	$                 50,000.00
Aang	Tesla	6-Feb-21	$                 25,000.00
Korra	RIOT	7-Mar-21	$                 15,000.00

Repayments

Manager	Company	Date	Repayment
Tarlock	Microsoft	5-Jan-21	$    30,000.00
Aang	Tesla	6-Feb-21	$    20,000.00
Korra	RIOT	7-Mar-21	$    10,000.00

Oustanding

Manager	Company	31-Dec-20	31-Jan-21	28-Feb-21	31-Mar-21
Tarlock	Microsoft	$  100,000.00	=100,000+50,000-30,000	calculate	calculate
Aang	Tesla	$    50,000.00	calculate	calculate	calculate
Korra	RIOT	$    10,000.00	calculate	calculate	calculate

And I basically need to calculate the total of the next month by using Oustanding of Previous Month + Disbursements - Repayments.

The issue is that every month I need to update the outstanding table with the real data. So how can I have a table with both static and dynamic data?

Any ideas on how to solve the problem? Thanks in advance!!

[amitchandak]

@Anonymous , with help from a common date table, try a new measure like

and common date , Manager and Company

calculate(sum(Table[Disbursement]), filter(allselected(date),date[Date] <= max(Date[Date])))
- calculate(sum(Table[Repayment]), filter(allselected(date),date[Date] <= max(Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi Amitchandak, and thanks for your reply.

I followed your steps (making the date calendar as per your video and marking it as date) and tried the formula but I am unsure what to do with that.

My issue is with adding the outstanding data

https://www.dropbox.com/s/j1vasiwigm03ra5/DummyData.pbix?dl=0 

(I updated the dummydate to a simplified version)

I have uploaded my dummy data, could you please take a look?