Tag: Power BI

Budget Checklist

O&M

RPA

QA

AFAOC Systems Training Calendar

Power BI ADOS Planning Tool Training

In this video, our team discusses conducts a training session on the new ADOS Planning tool. The tool utilizes Excel and Power BI to visualize three important data points our Wing Commander needs to drive decision making:

  • Historical – Because of the way PBAS now obligates orders and allocates quarterly funding into the future, our leadership needs to know the who, what, when of each Group’s budget. This is the easiest part since it’s a simple pull from the accounting system.
  • Planned – What is the plan for your remaining budget? Before you go to the boss, he wants to see how the Group’s plans on allocating the remainder of their budget.
  • Unfunded Requirements – Provide the who, what, when on the people Group can’t afford (UFRs).

I’m really excited about this tool and our new ability to visualize the Wing’s ADOS budget in one timeline.

MilTube-ADOS Planning Tool Training (CAC Required)

301FW Power BI Finance App (CAC Required)

301FW SharePoint RPA Budget (CAC Required)

Checkout Our Updated Power BI Finance App

I recently added a new ADOS Plan tab to our Power BI App. This tool utilizes a Gantt chart to visualize all orders throughout the year. This view helps Commanders see various duty statuses at a glance and see when their available funding runs out and UFRs begin.

I see this tool changing the way we fund ADOS UFRs and prioritize our needs each quarter. In addition, it helps my RPA analyst forecast quarterly needs much more efficiently.

301st Fighter Wing Power BI Finance App

AFRC/A1 Using Power BI for CUBE Data

I’m trying to highlight folks around the Air Force that are using Power BI. It’s gaining traction and it helps to see how other careerfields are displaying their data.

Here are a few of the data points they’re capturing:

  • Authorized vs Assigned Skill Level
  • Bonus Trends Reenlistment-Retrain
  • Career Field Health Slides
  • Commander Update Brief
  • CUBE Data Slicer Table
  • Fuse BI
  • Hist Retention
  • Retention Deep Dive

You can access the Power BI data set at https://bi.afrc.af.mil/reports/browse/A1Folder. Send questions or request access from AFRC/A1RI, Issues & Analysis Branch.

Power Query M Code – GTC Delinquency Report

One of my goals with Budget Dust is to create a community of data junkies. A group that uploads new ideas and improves each other’s code and documentation. GitHub is a code hosting platform that makes version control and collaboration easier. It lets you and others work together on projects from anywhere. Read this short tutorial on what branches are and how to publish commits. Don’t worry if those words don’t mean anything to you, it’s easy to pickup with a little practice.

The M Code below enables FMers to quickly transform, raw unedited data inside Power Query in a consistent, reliable process. Simply copy/paste the code into the Advanced Editor in the Power Query Editor in either Excel or Power BI. Then simply insert your local file path at line #2 where it says “INSERT_YOUR_FILEPATH_HERE” and then run the code. Voilà, it works!

Crucial rules to adhere to when using other people’s M Code:

  • Change the file path to your local machine or share drive
  • In most cases, make sure the file name does not change day-to-day
  • Download the file exactly the same way as the original code author intended

In future posts and YouTube videos, I will demonstrate step-by-step how to both implement the code and edit/improve the code on GitHub. As a community, let’s refine, improve and standardize our processes. In turn, we can create consistent reliable code used across the Air Force.

GitHub Repository:

https://github.com/budgetdust/GTC_DelinquencyReport

Tracking Documents Through the Stages of Accounting in DEAMS

After pulling a Status of Funds – Near Real Time report, use the Selective Transaction History Report to research any transactions.  Using the Discoverer Viewer report titled “R12 01 General Ledger Transactions STH – New_Payment Voucher Num” (STH report) returns all updates to documents in the system.  By pulling the data into a pivot table (snippet of the pivot parameters provided) users can see the dates and document details as it has moved through the system. 

Below are basic research details to follow:

Parameters used for the STH Report

Recommend pulling data by OE, FY and AAI (387700) to limit file size.

Insert the budget-affecting GL Account Codes (GLAC) (found at GLAC Parameter Strings (Cell A4)) to eliminate unnecessary General Ledger entries.

Update on the recent STH duplicates issue

The R12 General Ledger Transactions STH in Discoverer Viewer no longer contains duplicate lines of summary entries. Over the past month, DEAMS advised users to remove duplicate lines identified under the Je Header Id and Je Line Num fields. The STH now includes entries with more complete data. Although you may still see lines with matching Je Header Id and Je Line Num fields, those rows of information now include differences in other columns used to track documents from origination to payment. You no longer need to remove duplicates.

Power BI can handle years of STH data

In a future YouTube video I plan on demonstrating how to build an STH Tool using Power BI. I’ve pulled in every accounting transaction at my wing over the past five years and can finally harness the time intelligence functions available with DAX. You can do impressive day-over-day, week-over-week, month-over-month, or year-over-year analysis amongst other things. Chad Kukorola deserves all the credit for this idea. I had been dreaming of how to do this for years, but Excel always bogged down on me. After reviewing some of Chad’s Power Query code, I was able to make it work in Power BI. Until I get the YouTube video postd, reach out and let me know if you’re interested in seeing what the tool is capable of.