Determine latest condition of each equipment and show a month wise count


There are 100 machines in a factory.  Every machine has different test frequency. In a given month, not every machine is tested but we still have the last known rating (from some previous month) of that machine.  We have to show the latest rating of each machine for each month in a stacked column chart. This way, the total number will remain 100 every month in the chart, but the rating distribution (color based on legend) will change based on last available rating of that machine.

For example, in January, 35 machines were tested. So we have latest ratings of these 35 machines. But as the rest of the machines also have some previous rating, the graph needs to show all 100, with last available rating.

The expected result should look like this

You may download my PBI Desktop file from here.  The very same DAX formulas can be written in the DAX formula language of MS Excel as well.

Show Balance outstanding everyday even if data for everyday is not available


In this simple 3 column dataset, there are 2 accounts - Konto 1 and Konto 2.  Each account has a balance outstanding as on a certain date.  However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020.

The objective is show the balance outstanding every day.  For days which are absent from the dataset, the balance outstanding should be the balance as on the previous day.  So for the Konto 1 account, on January 4-5, the balance should be 400 and on January 9-10, it should be 250.  The same logic applies for the Konto 2 account as well.  The expected result should be

I have solved this problem using Data > Get & Transform and PowerPivot.  You may download my solution workbook from here.

Here's another related question.  Given this 3 column dataset, the objective is to determine the total of the "PDV Combined Total" column for the past "5 billing days" from today - the past 5 working days have to be determined by looking at the "IsABillingDay" column.  So if today is December 4, 2020, then the past 5 business days would be November 30, 2020 to December 4, 2020.  The result should be 14,95,937.  However, if today was December 2, 2020, then the past 5 business days would be November 24, 2020 to December 2, 2020.  The result would be 14,33,545.  You may download the solution in a PBI file from here.  So while this question has been solved using the DAX formula language in PowerBI Desktop, since the same formula language exists in MS Excel as well, this result can be obtained in MS Excel as well.

Analyse membership changes from year to year


Assume a simple 4 column dataset as shown below.  This data shows which ID had which type of subscription in which year.  So ID A, which started as a "Free" subscriber in 2018 switched to a "Premium" subscriber in 2019 and then churned out in 2020.  Likewise, ID D which started as a "Pro" subscriber in 2018, churned out in 2019 but returned as a "Free" subscriber in 2020.
The objective is to study how subscribers switched from one subscription type to another across year.  So the expected result should look like this

I have solved this question using the PowerPivot.  You may download my MS Excel workbook from here.

Calculate rolling sum for the past week by ignoring blank cells


Assume a simple dataset as shown in the image below (the input data is in columns A and B only.  The desired outcome is in columns C and D).

The objective is to calculate the 7 days rolling sum and average (as shown in columns C and D) ignoring blank cells.  So in cell C8, the rolling sum is the summation of values from range B2:B8.  In cell C9, it is from B3:B9.  However, in cell C10, it will be from range B3:B9 (not from range B4:B10).  Likewise, in cell C11, the rolling sum will be from range B4:B11.  So the range to be considered for calculating the rolling sum has to roll back automatically until it picks up 7 numeric cells - the blanks have to be ignored.  The rolling average is a simple division - Rolling sum/7.

I have solved this question with Excel formulas here.  This time however, I am sharing a solution by using the DAX formula language available in the PowerPivot and PowerBI Desktop.  You may download my PowerBI Desktop file from here.  The same solution can also be obtained in MS Excel using the PowerPivot as well.

Customer analysis by Country and time period


Here is a Sales dataset of 8 columns and 29 rows.  It basically details the revenue earned and cash collected by service type, Customer, Country and Period.  For a selected Country and time period, there could be customers availing of both services or of any 1 service.

There are 2 broad questions that one may want to get answers to:

  1. Determine the number of customers who availed of a certain number of services
  2. Determine customers with whom business was forged for the first time and those who churned out

For a chosen country and Year/Month, the first question stated above further sub-divides into:

  1. How may customers availed of both services - Consultancy and Implementation
  2. How may customers availed of only one of the two services

So if a user selects the Country as India and Year/Month as January 2015, then Customers who availed of both services would be 1,3 and 4.  Note that Customer 2 should not be considered (even though he/she availed of both services) because the revenue earned from one of the services (Implementation) was nil.  For the same selection (India and January 2015), the Customers who availed of only 1 service would be Customer 2 - this customer availed of only the Consultancy service (Revenue was earned from this Customer only for this service).  After applying a filter on the source dataset, the rows for India and January 2015 are:

The expected result is shown below in PowerBI desktop software.  If you are not concerned with who those customers are (you just want the count), then you may simply remove the Customer Name field from the visual.

The second question is to determine the number of new and lost customers.  If a customer was not in the database in any prior month, the customer is identified as new.  To clarify, a customer who availed of the Consultancy service in a prior month also availed of the Implementation service for the first time in the current month would not be counted as a new customer.  If a customer ceases to generate revenue in any month, the customer would be counted as lost (churned) in that month.  So when USA is selected in the Country slicer and Year/Month is February 2015, the expected result is:

I have solved this question with the help of the PowerPivot.  You may download my PowerBI desktop solution file from here and source Excel workbook from here.  This problem can also be solved in MS Excel using the PowerPivot.

Flex a Pivot Table to show data for x months ended a certain user defined month


In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years - 2017 and 2018.

The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month.  So, if a user selects the Year as 2018, Month as June and Duration as 9, then the Pivot Table should show month wise demand charge for the 9 months ended June 2018 i.e. from October 2017 to June 2018.  Likewise, if a user selects Year as 2018, Month as May and Duration as 3, then the Pivot Table show should month wise demand charge for the 3 months ended May 2018 i.e. March 2018 to May 2018.

You may download my solution workbook from here.

Show sales only for corresponding months in prior years


Refer to this simple Sales dataset


The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year.  For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4.  As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is


You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Determine the most recent status after satisfying certain conditions


Assume a three column dataset with Patient ID, Smoking Status and Review Date

PatientID SmokingStatus ReviewDate
P1 10-03-2018
P1 9 09-03-2018
P1 1 08-03-2018
P1 4 07-03-2018
P2 9 10-03-2018
P2 9 09-03-2018
P2 9 08-03-2018
P2 9 07-03-2018
P3 2 10-03-2018
P3 09-03-2018
P3 9 08-03-2018
P4 9 10-03-2018
P4 1 09-03-2018
P4 4 08-03-2018

The objective is the create another 3 column dataset with the following conditions:

  1. If the patient's latest smoking status is other than Blank or 9, then consider that as the smoking status of the patient; and
  2. If the patient's latest smoking status is blank or 9, then consider the previous smoking status that is not blank or 9; and
  3. If the patient's smoking status is blank or 9 on all dates, then consider the smoking status as 9

The expected result is:

PatientID Last date when the smoking status was other than 9 or Blank Smoking status on that date
P1 08-Mar-18 1
P2 10-Mar-18 9
P3 10-Mar-18 2
P4 09-Mar-18 1

I have solved this question using 3 methods - PowerPivot, Advanced Filters and formulas.  You may download my solution workbook from here.

Compute “running total in” across years in a Pivot Table


Assume quantity sold date by date and City in a three column database.  The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table.

The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running Total in, resets the quantity sold to 0 when the year changes.

This issue can be overcome by writing DAX formulas in a Power Pivot.  You may refer to my solution in this workbook.