Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
krisso
Regular Visitor

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 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.