Formatting Dates in Power Platform
06 Feb 2019
Sometimes Date format is very confusing for me, be it written on paper or online. Let’s consider a date 06-02-19. So, is it 2nd of June or 6th of February or 19th of February? Microsoft applications have versatile functions to change the format how day, month and year are arranged together to form a complete date. I used few of such easy to use functions in Excel, Flow, Powerapps and PowerBI. There is bunch of predefined functions in each of these applications to change representation of text and particularly date. Note that functions listed in this blogpost are primarily used to change the format of date and not to take other insights of data.
Global Landslide Catalog
28 Oct 2018
I remember it was July 2014, another rainy day in Pune. I was at work in the evening and a colleague showed me photos of Malin incident. It was scary. A landslide incident which took place early in the morning of 30th July killed around 150 people in small village called Malin not much far from Pune city, in Maharashtra state of India. Continues monsoon rain caused mudslide on the slope of mountain and whole village had gone under mud and debris. Soon after that “Satark India” https://goo.gl/AFvnds an amateur group built their own model to predict such incidents and spread awareness through online and print media. Related to this, earlier this year NASA had updated open data named Global Landslide catalog that has all information of landslides from 1988 to 2017. Below interactive visualisation is created using Microsoft Power BI based on OData feed of dataset published by NASA.
Telecom Subscribers Report
23 Oct 2018
Understanding Subscribers distribution is one of the important aspects of service management. It can be helpful in driving business management and network management strategies. Here I have tried visualizing telecom subscriber report using Microsoft Power BI. It shows how diverse customer profiles are, according to their age, gender, dependencies & the services being consumed also in turn how it contributes to Total Revenue. The clear view of Churn Status (customer stopping/opting out the services) and in depth knowledge of services consumed and revenue impacted/generated by Churned customer can be useful to define new retention plans. This report also gives functionality to view complete data of individual subscribers as well. This dataset is taken from Kaggle (Link mentioned in report)
Vehicles Registered Under Pune Munciple Corporation - India
21 Sep 2018
A Power BI report below is interactive visualization of number of vehicles registered under Pune Municipal Corporation in Maharashtra state of India from Year 2002 to 2017. Dataset is taken from PMC open data. Number of vehicles can be considered as one of the key parameters to analyse the growth of a city. Simple 16 rows of data represented by Charts, matrix and cards can showcase insights to great extent about the development of Pune city. It also gives sneak peek of ten years into the future. This report consists of four pages.
Data
First page of Power BI report is a Matrix of numerical values. Header is type of vehicles and First column is year, last column is total number of vehicle year-wise. Below the Matrix A “card” shows a round off value (in Millions) of measure “Total Number of Vehicles”. This value changes as per the row selected in Matrix, showing total of that particular year. When not selected any row, Card shows 31 Millions and when selected year 2009, total number of vehicle is 2 Millions where as exact value in Matrix is 1760402.
Charts and Treemaps
On second Page, Treemap show Motorcycles, scooters, mopeds and cars are the major contributors of total value. So to understand exact values of contributors, two different Line Charts are used. For better visualization one line chart is for Two wheelers like motorcycle, scooters & mopeds.Other line chart is for cars, jeeps and auto rickshaws. Here motorcycles and cars show linear growth compared to mopeds, autos respectively. Above these charts two column charts with Year on X axis shows growth of total number of vehicles and total number of new vehicles added every year. By hovering mouse on these charts, detailed values can be seen and by clicking on it, all other charts and treemap highlights their corresponding details.
Positive and Negative Trendline
On third page, insights of few vehicles which have positive and negative trendline. Decrease in number of auto-rickshaws and stage carriage after year 2011 is significant as at the same time there is increase in school bus started which continues to grow even more after Year 2015. Tankers and station wagons has positive and negative trendline respectively. Contract carriage has ups and downs but value of year 2017 is almost two-fold as that of year 2016.
Forecasting
On last page, by using built-in forecasting models two line charts shows growth of motorcycles and total number of vehicles for next 10 points i.e. till year 2027. Confidence interval is set to 95% so prediction value have upper bound and lower bound values as well.
Implemention
For implementing this visualization, excel connector was used in Power BI desktop. Basic formatting done using power query UI. There was no need of M functions as data was clean, no missing values were present. During analysis total number of vehicles and new vehicle calculations were done by various DAX function like IF, SUMX, CALCULATE and PREVIOUSYEAR. This report was published to Power BI service and report URL is embedded in this blogpost.