Forum Discussion

krisso's avatar
krisso
Regular Visitor
9 years ago

New user

Im really new to Power BI and struggling on a couple of things and wondered if I could get some guidence.

 

I used to do all the following calculations via pivot tables in excel using a sharepoint version, but our IT people have said that this is causing the spreadsheet to crash and advised I use Power BI for this.

 

I have had no problems in creating the tables from the data in the excel sheet and linking them together but I am having problems from here.

 

On my pivots sheet I used to have 3 pivot tables that would look at data from 3 worksheets. This would be for a cashflow purpose so that I could see what was happening in the next 14 days. From this I would use a vlookup to pull out data from the pivot tables based on todays date and then enter a starting bank balance and the days cheques that had cashed through so I could forecast an end of day figure. Is there anyway I can do this in Powe BI?

 

Thanks

Chris

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    There answer is probably "yes" but would have to see some sample data and some images or something of your expected results.

    • krisso's avatar
      krisso
      Regular Visitor
      9 years ago

      Thanks for your reply. My excel data looks like this

       

      Date         
      16-Feb-17         
      Barclays Starting Balance£2,647,717.02Manually Entered       
      Todays Payments653,428.61Calc from look up from todays date       
      Todays Reciepts960,850.00Calc from look up from todays date       
      Cheques Cashed6,321.15Manually Entered       
      Predicted Barclays Balance£2,948,817.26        
                
                
                
                
      All But ADF   ADF   Unallocated Funds 
       Values   Values  Row LabelsSum of Amount
      Row LabelsSum of To Send Out £Sum of Expected In £ Row LabelsSum of To Send Out £Sum of Expected In £ 13/02/20171,510.00
      15/02/2017620,807.61  17/02/2007240,716.92  14/02/20171,535.00
      16/02/2017249,995.40344,468.13 16/02/2017403,433.21615,871.87 15/02/2017107,471.00
      17/02/2017557,695.62802,894.92 17/02/2017191,720.95110,000.00 16/02/2017510
      22/02/2017 8,001.89 23/02/2017 73,415.00 Grand Total111,026.00
      23/02/2017 1,264,610.00 24/02/201791,500.00    
      24/02/20171,177,650.00275 Grand Total927,371.08799,286.87   
      27/02/2017 205,767.40       
      28/02/20171,847,925.001,700,779.00       
      03/03/2017 37,703.62       
      (blank) 30,557.06       
      Grand Total4,454,073.634,395,057.02      

       

       

      The 3 tables below feed into the top table as shown in the calculated fields from a vlookup. We manually enter the days starting balance and the cheques that have cleared through the bank. I just couldnt see how to replicate the top section in this excel spreadsheet on BI. I have imported the data but that is as far as I got. I couldn't see how to pull data based on todays date into another table and manually enter the figures I needed for the daily forecast

       

      Thanks