Posts

Showing posts from April, 2020

RPA with Dynamics 365 Finance & Supply Chain

Image
Are you looking for ways to automate some of your routine accounting functions, like reconciling sub ledgers to the general ledger?   Microsoft has recently created some examples using Power Automate Desktop (think of it like Task Recording on steroids). It can not only automate steps through Dynamics 365, but it can also automate Excel and other applications. In one example from Microsoft they automate the AR to GL reconciliation: Automatic running of the AR aging and the customer/ledger reconciliation reports. Power Automate then extracts data from both reports in Excel and compares the balances. Power Automate then posts a Team message letting you know the subledger is in balance. Power Automate saves the reports with the date in the file name, so there is backup. Microsoft Directions & Example   I working with the example, I needed to make a couple of adjustments to the sample flow that was provided but I was able to get it to work. Here is a video of the flow running in

Covid Dashboard - Article 6 - Creating a group for a slicer

Image
I will continue to build on my earlier posts about my  COVID19 Dashboard .    In this series, I have written a few different blog articles describing not only how I built it, but some things I learned in the process. The current list of topics include: Working with growing CSV files Using index and merge when transforming data Using time functions to calculate the daily change Reporting based on the population Working with maps, setting data types correctly Today I am going to look at a question one person asked me.  When they were using the dashboard, they wanted to be able to filter the global results by countries of similar size. After thinking about this for a while, I decided on the following approach.  There are probably different approaches that can be used, and that is one of the things I really like about Power BI.  In this case, I tried to take a very simple approach. Step 1 - I had to decide how I wanted to group the countries.   I took a look at

COVID 19 Dashboard – Article 5 – Working with maps

Image
I will continue to build on my earlier posts about my  COVID19 Dashboard .    In this series, I am writing a few different blog articles describing not only how I built it, but some things I learned in the process. The current list of planned topics include: Working with growing CSV files Using index and merge when transforming data Using time functions to calculate the daily change Reporting based on the population Working with maps, setting data types correctly Possibly more to come depending on how long this all goes…. PART 5 – Working with maps I will be the first to admit, I have not used the mapping features in Power BI very often.   I have used them for some simple demos, but being an accountant, I am more often interested in the numbers versus the visual location.   However, when analyzing a global pandemic, I felt a map visual or two might be important. So without going to any custom visuals, there are four map visualizations in Power BI.   Map Fille

COVID 19 Dashboard – Article 4 – Reporting based on population

Image
I will continue to build on my earlier posts about my  COVID19 Dashboard .    In this series, I am writing a few different blog articles describing not only how I built it, but some things I learned in the process. The current list of planned topics include: Working with growing CSV files Using index and merge when transforming data Using time functions to calculate the daily change Reporting based on the population Working with maps, setting data types correctly Possibly more to come depending on how long this all goes…. PART 4 - Reporting based on population In looking at the data, I quickly realized simply looking at total numbers or even daily changes was only giving me a limited view of what was happening.     Were the large number of cases in New York simply because they have more people than most other states, or were they growing at a different rate.   Are New York citizens at a higher risk of acquiring the virus than California citizens.   It became evident

COVID 19 Dashboard - Article 3 - Adding some key measures - unique time functions

Image
I will continue to build on my earlier posts about my COVID19 Dashboard .   In this series, I am writing a few different blog articles describing not only how I built it, but some things I learned in the process. The current list of planned topics include: Working with growing CSV files Using index and merge when transforming data Using time functions to calculate the daily change Reporting based on the population Working with maps, setting data types correctly Possibly more to come depending on how long this all goes…. PART 3 – Adding some key measures In my earlier posts, I spent time explaining where I acquired the data and some key steps I took in transforming the data into the data model.   In this post, I will explain some of the DAX I used to create some of the key measures for the visualizations.   One of the first steps in design is to identify what questions you are trying to answer or the story you are trying to tell with your visualizations and reports.

COVID 19 Dashboard – Article 2 - Transforming the Data – Calculating between rows in Power Query

Image
I will continue to build on my earlier posts about my COVID19 Dashboard .   In this series, I am writing a few different blog articles describing not only how I built it, but some things I learned in the process. The current list of planned topics include:   Working with growing CSV files   Using index and merge when transforming data   Using time functions to calculate the daily change   Reporting based on the population   Working with maps, setting data types correctly   Possibly more to come depending on how long this all goes…. PART 2 – Continuing to transform the data In my first post, I explained how I brought in data from a CSV file and used “Unpivot Columns” to transform the data into a format that works much better for reporting.   Once the data was in this format, I quickly noticed that the values (Cases, Deaths and Recoveries) were cumulative.   In other words, these amounts were ending balances not daily transactions, if I look at it as an accountant.