In this post, I will talk about the importance of the Cash Position Report for any Professional Services Organization.  The Cash Position is your current and forecasted ‘Cash in Hand’. The importance of having Cash in your bank account cannot be emphasized enough. It is important to understand the difference between Cash Flow and Cash in Hand as they are not the same. An organization can have a solid cash flow (which is revenue tied up in Receivables), but without money in the bank, it cannot operate. Let me go into this further as it’s the foundation of our conversation here.  For any Professional Services organization, anywhere from 60 to 80% of the Monthly expenses are tied to Employee salaries. Paying salaries on time, every month is key to the organization’s credibility. Salaries need to happen on time, month after month, irrespective of your cash flow. They require cash on hand. In addition, any business planning, from HR Activities (a team lunch, an offsite meet) to Investing in the organization’s growth (new areas of business, developing expertise) also requires cash on hand.  One more topic that I need to mention is the current pandemic. Many Projects are delayed, cancelled or on hold. From a business standpoint, this kind of unforeseen event can put a tremendous strain on its ability to continue operations. But while the current pandemic is an extreme scenario, businesses constantly face challenges as we operate on a global level (2004 Tsunami, 2008 Financial Meltdown are some recently examples). Cash on Hand can mean the difference between survival and going out of business.  So we see that Cash on Hand is a Key Metric and hence should be reviewed regularly, ideally during a scheduled Management meeting on a recurring basis. At CloudFronts, we do this review every Monday morning. Our two and a half hour Monday morning Management meetings are literally run by numbers. Some of the Key Reports we review are Project Overview Report, Team Billable & Non-Billable Allocation Report, Support Metrics, Sales Pipeline, Outstanding Accounts Receivable Report, Forecasted AR Report, Cash Position Report. Each one of these is a PowerBI Report embedded into our Microsoft Teams Leadership Team Group.  So what is the Cash Position Report? I will start with a simple equation and then delve into the details –   Cash Position = Cash + AR – AP + Projected AR Now let’s walk through each of the Items in there and how we get that data – Cash on Hand – This is what is in your Bank Account when you run this Report (our report is updated every Monday morning before our weekly Management Meeting). We manually enter this data into the Report.  AR – AR is your Accounts Receivable which includes all Customer Invoices that are due this month and have not yet been paid. This includes overdue invoices carried forward from the previous month as well. If an Invoice is Paid, then it goes into Cash on Hand. Our AR data comes from Dynamics 365 PSA Contract Invoicing Schedule for both Fixed Bid and Time & Materials Projects. Our Contract Invoicing Schedule is the Single source of truth for all our Project Services Invoicing.  AP – AP is all Payables this month, including Salaries. It comes from our Accounting System which is currently QuickBooks Online.  Projected AR – Projected Accounts Receivable are any Invoices that we will generate this month and that will be due this month. This data also becomes from our Contract Invoicing Schedule. For Fixed Bid, this is based on the Contract Billing Milestones and for Time & Materials, this data comes from the Team Member Allocations.  At the top of the Report, you will see that the report is month wise. Hence as you move into the future months, you have less visibility into AR data, but rely on your Projected AR data. As you move into a month, the Projected AR starts becoming AR, which then becomes cash on hand and typically by the end of the month, Projected AR should be zero.  This Report gives the Management Team a clear visibility of the company’s Cash Position over a period of time. For us, we are looking at six months into the future, but organizations can easily set this up for longer periods. By reviewing it regularly, in a scheduled meeting, you ensure there are no surprises and you have time to react well in advance.  The benefits of such a report are further amplified when you consider its impact on our behaviour. During the pandemic, our No.1 priority has been to ensure we are a stable organization with the ability to operate through this pandemic. This report can give you that stability and allow you to stay productive and impact your team with a positive state of mind.  If you are keen on understanding more about our Internal Systems that keep us running and growing, our Internal Systems page is a wonderful resource – https://www.cloudfronts.in/our-internal-systems/ CloudFronts is one of the leading Microsoft Partners globally on implementing Professional Services Automation on the Microsoft Dynamics 365 Platform. You can reach me on LinkedIn for any questions/queries or email me at ashah@cloudfronts.com

Steps: Open Xrm Toolbox and add Tool “PowerBI option-Set Assistant” Open the tool and Click on load entities, it will load all the entities. Select the entity and field name from which we want to retrieve data, click on “Create records for selected option sets”. It will prompt that it will create new entity, click on Yes and proceed.it will create a new entity for option set. Now go to power bi and create connection using OData feed. Now we can see,”gap_powerbioptionsetrefs” entity created and we can load this into power bi model. Hope this helps!

In this blog we will learn how to get a last refresh date in Microsoft Power BI report. So, using this practice, we will know last data refresh and also knows the problem with dataset refresh. In order to get latest refresh dates, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on Transform data, it will open Power query Step 3: Click on New Source, select Blank query. Step 4: Enter DAX expression: = DateTime.LocalNow() and also change the name Step 5: Before use its need to convert into To Table. Step 6: Rename the column name like DateTime Step 7: Click on Close & Apply Step 8: Drag the Datetime field in report and apply card level visualization. It will work when you refresh the whole model, it will not refresh the date when individual table refresh. Hope this helps!

While viewing reports in Power BI service/ Power BI Desktop, Power BI provides a feature to eliminate the filter pane completely in order to provide a professional look. Here are the ways to Hiding Filter Pane in Power BI reports. 1.Show/Hide the filter pane from report readers. By default, the Filters pane is visible to the end-users. If we don’t want them to see it, we can select the eye icon next to Filters and hide it. 2. Turning on/off the filters from Settings. Steps: Open the Power BI Report in the desired workspace and go to the content section. Select More options (…), then select Settings for that report. Go to the Filtering Experience and disable both the options to hide the filter pane. 3. Showing the desired filters in a user-friendly manner: We can select the filters which we want to display to the end-user depending on the users requirement. Hover-over the filter icon on the right-hand corner to view the read-only filters. Whichever filter we do not want to display to the user, we can hide them in the filter section of the report. Hope this helps!

Power BI has some new features and Future Promises for Modern Enterprise applications in Business.

In this blog we will learn how to hide the filter panel in Microsoft Power BI report. Once you publish the Power BI report in Power BI service, filter panel is useless for the end user. In order to hide the filter pane, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on filter pane. Step 3: There is a eyeball icon at the top of the filter pane that can be toggled. Save the report and publish to your workspace. Before published the report looks like below. After the published the report, user can not see the filter pane. You can also do this in the Edit Report in the service. Hope this helps!

Dashboard is created to get a brief overview of your report by pinning visuals to an Existing dashboard or to a new dashboard. But sometimes it might be required to pin all the visuals of your page in the report to your dashboard. This blog will guide you through how this can be achieved.

Instead of viewing dashboard and report on Power BI Web Service, we can directly view it in CRM. We need to publish the Power BI dashboard to our CRM Environment. This Blog will guide you through it can be done.

In this blog we will learn how to append two or more table into new table or existing table. The Append Queries in Microsoft Power BI is an equivalent of UNION ALL in SQL. Consider two sample customer table; one for CustomerOne: And Customertwo: Open Microsoft Power BI for Desktop > Get Data > Excel > the excel file. You should see this: Select tables and click on Transform Data. Now it’s time to proceed with the Append operation itself: Click the little triangle on the main “Append Queries” button. You’ll get 2 options: Append Queries – this operation would add rows into an existing table Append Queries as New – this operation will create a new output table from 2 (or more) appended tables. I’m going for this option. So, let’s see what happens after clicking the “Append Queries as New” button: You either append 2 tables like me OR you can do “Three or more”. So, keep in mind you are NOT limited to 2 tables only. One important thing to understand how the tables are actually “appended” together. Power BI looks at column names. If it finds the same columns like in my case (CustomerID, First Name, Last Name, Contact No) in both the tables, it won’t create any new columns and it will fit everything right into those 4 columns. What about Duplicates? Append Queries will NOT remove duplicates. You have to use Group by or Remove Duplicate Rows to get rid of duplicates. So, you press OK and you get the result of your operation. Now the result: Hope this helps!

This Blog will show you how you can Install and locate your Plugins in XRM new interface.