Happy Holidays from the Dynamic Budgets Power BI Team!

Wishing you and your colleagues the very best for the Holiday Season.
We’ve put together a sample PowerBI report for you to work with during your down-time.  
 
3 links to Choose From:

 

Below is a summary of the components used and some details about the data sources:

 

First Tab: Current Year Revenue and Expense by Month

This tab Uses 1) Hierarchy Slicer, 2) Timeline slicer, and 3) Chicklet Slicer to filter the results in the bar chart.

 

 The hierarchy slicer is straight forward and allows for multiple levels of a hierarchy by simple drag and drop 
 

The Timeline Slicer offers a granularity setting in the format tab to offer Year, Quarter, Month, Week or Day details.
The trick for this slicer is that you need to add a Date field as the primary data field for the slicer, it will automatically generate its own hierarchies, not need to prebuild a hierarchy or overthink this…

It also offers format settings for the definition of a fiscal year beginning month/day and first day of the workweek.
      

The Chiclet slicer is another interesting slicer with a number of formatting options, you will first want to go to the format tab, general section and specify the orientation, and number of columns and rows of data you wish to show.

Data Sources:

  • Account Summary is a union of the GL10110 and GL10111
  • Actual Details is a union of the GL20000 & GL30000 transaction detail tables
  • Budget Details is a query of the GL00201 table (the monthly budget values per account)
  • Dates is a virtual table built by a dax function
  • Reporting Labels – Typically in Dynamic Budgets we would leverage our 45 user defined fields at (OrgUnit, Object and GL Account) levels,  but for this sample report we hardcoded reporting categories to account ranges from a 3 way join of the GL00100, GL00102, and GL40200, you should be able to follow the comments in the source query. We would suggest you replace this query source and create either an excel file or a SQL data table with ACTINDX, ACTNUM and whatever reporting labels at the department, natural account, and GL Account level would be helpful for your reporting.

 

  • Measures folder: There are several measures in the dashboard:

    We left the measures within their respective tables, so you know the main sources of the measures. As you play with the data and visuals you may want to add your own measures. We added a Measures folder allowing you to keep things tidy. As your list of measures increases, feel free to move them from their various tables to the Measures folder. Select the measure to move and reassign Measures as the new Home Table under the Modeling tab:

Second Tab: Current Year Budget by Department

  • With Fabrikam data we are seeing a Christmas tree like shape on the left. This is the funnel visual. With your data this shape may change hopefully – data is sorted ascending by budget amount. Hence, revenues should show on top of your visual.
  • To the left we utilize a monthly matrix with standard P&L layout. Rows are drilling into further detail by account category.
  • If you populate these visuals with your data you need to filter the page by desired budget year as well:

Some Other Cool Stuff:

One of the great benefits of Power Bi is the flexibility of visuals. Developers seem to have no limit on their creativity when it comes to crafting them. Here are some examples we like in this dashboard:

  • Selected Year Actuals tab: The Line Chart by Akvelon! On this page, users pick the year to display actuals by month. In addition to selected year and months, the visual splices out individual line charts for account categories. This enables a quick and easy comparison between various categories to easily determine, which categories may be driving revenues and costs.
  • Actuals by Categories tab: Similar to their line chart, Akvalon also provides a stacked column chart visual. Users can instantly review and compare various account categories over several years. Top version arranges categories on rows. Users scroll up and down to see the development of each category over the years. Bottom version arranges categories in column format. Users would scroll left to right. This would be helpful with a smaller number of categories.
  • In our sample we arranged data by Date on the x-axis. If you change the Date to Date Hierarchy you will be able to further drill into your data for additional analysis and arrange initial layout by year:


Current Year Budget vs Actual Revenues tab:

  • Last but not least, we are excited that there is a visual that incorporates column/line charts with data tables! Definitive Logic created this visual a lot of us have been missing from Excel spreadsheets. On this page, users can pick their year of actuals versus budgeted revenues to display. At the bottom of the graph we showing the corresponding data table..



  • Note:  This visual has a glitch with number formatting, if you apply comma format to the value fields, the bars and lines will disappear.  The developers are aware of the bug and hope to have a fix available in the next few weeks.