Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Rolling forecast(?) question

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

ManagerCompanyDate Disbursement 
TarlockMicrosoft5-Jan-21 $                 50,000.00
AangTesla6-Feb-21 $                 25,000.00
KorraRIOT7-Mar-21 $                 15,000.00

Repayments

ManagerCompanyDate Repayment 
TarlockMicrosoft5-Jan-21 $    30,000.00
AangTesla6-Feb-21 $    20,000.00
KorraRIOT7-Mar-21 $    10,000.00

Oustanding

ManagerCompany31-Dec-2031-Jan-2128-Feb-2131-Mar-21
TarlockMicrosoft $  100,000.00=100,000+50,000-30,000 calculate  calculate 
AangTesla $    50,000.00 calculate  calculate  calculate 
KorraRIOT $    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!!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?