DAX : Building a monthly salesreport with estimation in PowerPivot (Part III)

Introduction

This is the third blog post about the Daily Sales dashboard that I'm currently building in PowerPivot and in PowerView (Excel 2013). I am continuing to learn more and more about DAX expressions. Everytime I go a step further in the development of the PowerPivot workbooks. I see new opportunities and using better DAX expressions. This blogpost describes an evolution in the development of a daily sales report rather than a subsequent blogposts of other blogposts I have written about this subject, so far:

• DAX : Building a monthly salesreport with estimation in PowerPivot (Part I)
• DAX : Building a monthly salesreport with estimation in PowerPivot (Part II)
• DAX : Building a monthly salesreport with estimation in PowerPivot (Part III)

The case

The purpose of the workbook is analyzing the daily sales in a month. I want to see the sales in the current month but also in the previous month. I have the following requirements :

• Report the sales in the current month in days.
• Report the sales of the previous month in days.
• Report the net revenue of the month so far (Month to Date).
• The projected net revenue for the rest of the month based on the Month to Date.
• The projected net revenue in a month (based on the MTD) vs the Same Month last Year.
• One tab for the sales per day.
• One tab for the sales per Brand (a collection of products).

There are some considerations that makes it interesting:

• Products sold in the current month or previous month may not have been sold in the same month previous year.
• Products that have been sold in the past may not sell in the current month or sold in the previous month.
• Weekends and holidays should be excluded from the calculations. For example, if the day is a holiday it is not a working day (this is has impact on the projected sales).

There are some limitations:

• The clients are 32bits machines. Therefore we can't store all the data in the PowerPivots because of memory limitations. For this reason I've created a view for the current month, previous month, same month last year and the previous month. So I've included 4 periods in my data to limit the amount of the data.

The Data

For this blogpost, I have created some test data for the 4 periods that I want to simulate. I have created three tables, two dimensions and one facttable.

Calendar

For this blogpost, I have created an Excel spreadsheet that contains the most relevant data. To make life easier I included an extra column WorkingDay that is a walking number that doesn't add up when it's a weekend or a holiday. 

Product

The Product dimension is a simple product table with Productdescriptions and brandnames.

Sales

The FactSales table is the fact table with the references to the dimensions and the measures in this case SalesQTY and UnitPrice.

The datamodel

In this mock up there are three tables in the PowerPivot workbook: DimProduct, DimCalendar and FactSales. The data model is as follows:

Month Selector

The next step is introducing a month selector in the workbook. As I've mentioned in my previous blogpost, the monthselector selects the current month and a previous month based on the actual data in the Fact table.

=IF(
 DimCalendar[MonthNumber] = MONTH(LASTDATE(ALL(FactSales[CalendarDate]))) && 
 DimCalendar[Year] = YEAR(LASTDATE(ALL(FactSales[CalendarDate]))), 
 "Current Month",
 IF(
    DimCalendar[MonthNumber] = MONTH(EDATE(LASTDATE(ALL(FactSales[CalendarDate])), -1)) && 
    DimCalendar[Year] = YEAR(EDATE(LASTDATE(ALL(FactSales[CalendarDate])),-1)), 
    "Previous Month"
 )

As you may see in the screenshot, the monthselector not only selects current month and previous month but also the other months (that I've included for same month last Year calculation) I want to exclude this from my selection.

The logic I have used in my month selector should also be included in the SumNetRevenue DAX Expression. To remove an error message I've also added the MAX() function in the DAX expression.

SalesAmount:=IF(OR(
 MAX(DimCalendar[MonthNumber]) = MONTH(LASTDATE(ALL(FactSales[CalendarDate]))) &&  
 MAX(DimCalendar[Year]) = YEAR(LASTDATE(ALL(FactSales[CalendarDate]))), 
 MAX(DimCalendar[MonthNumber]) = MONTH(EDATE(LASTDATE(ALL(FactSales[CalendarDate])), -1)) &&  MAX(DimCalendar[Year]) = YEAR(EDATE(LASTDATE(ALL(FactSales[CalendarDate])),-1))
                  ), 
                SUMX(FactSales, FactSales[SalesQuantity] * FactSales[Unitprice])
              )

MTDNetrevenue

The next step is adding a Month To Date Net Revenue measure for the revenue in the current month and in the previous month.

       
MTDNetRevenue:=CALCULATE([SalesAmount], DATESMTD(DimCalendar[CalendarDate]))
 

This results in the following pivot:

As you may see in this pivot is that the MTD continue until the end of month and that is not really desirable. Therefore we need to adjust the DAX expression a bit.

MTDNetRevenue:=IF(ISBLANK([SalesAmount]), 
                  BLANK(), 
                  CALCULATE([SalesAmount], DATESMTD(DimCalendar[CalendarDate]))
                 )

And now the empty cells for net revenue are gone.

Let's see what happens for the previous month.

Projected Net Revenue

The next step is to calculate the projected net revenue for the current month. This is a calculation of an estimation of the sales based on the current MTD netrevenue or the rest of the month. This way it's possible to determine whether the sales are on track on or not. The calculation for the projected netrevenue is:

MTDNetrevenue * SumOfworkingDaysPerMonth/CurrentWorkdayoftheMonth

Let's build this in a DAX Expression:

ProjNetrevenue:=DIVIDE([MTDNetRevenue] * [SumOfWorkingDaysPerMonth], 
                        MAX(DimCalendar[WorkingDayInMonth]))

SumOfWorkingDaysPerMonth:=CALCULATE([NumOfWorkingDaysIncHolidays],
                              ALL(DimCalendar[CalendarDate]), 
                              ALL(DimCalendar[DayOfMonth]))

This is for the current month:

And the screenshot below is for the previous month. You can compare the estimation and the actual values and validate the predictions.

Let's check a value and see if the calculation is right. We take the values of 19/1/2016. This is the twelfth working day in the month and the total number of working days in the month is 20.

 ProjectedNetrevenue = 161.5*20/12 = 269,17

And this is correct.

Now we want to compare this with the complete month but in the previous year for the current month and the previous month. Now, I've written the following DAX Expression to solve this:

SMPYNetRevenue:=IF(ISBLANK([SalesAmount]), 
          BLANK(),
          CALCULATE([NetRevenue], 
          FILTER(ALL(DimCalendar), DimCalendar[Year] = MAX(DimCalendar[Year]) -1), 
          FILTER(ALL(DimCalendar), DimCalendar[MonthNumber] = MAX(DimCalendar[MonthNumber]) )
                   )
          )

Or use an improved version (with PARALLELPERIOD):

SMPYNetRevenue:=IF(ISBLANK([SalesAmount]), 
                   BLANK(),
                   CALCULATE([NetRevenue], 
                       PARALLELPERIOD(DimCalendar[CalendarDate], -12, month) 
                            )
                   )

This results in the following pivot (for the current month)

And this is for the previous month

Now we can calculate the %projectedvsSMPYNetrevenue

%ProjectedvsSMPYNetRevenue:=DIVIDE([ProjNetrevenue] - [SMPYNetRevenue], [SMPYNetRevenue])

And this results in the following pivots (for the current month):

For the previous month:

NetRevenue by Brand

In the former paragraphs we discussed the net revenue split by day. In this paragraph we add an extra tab to the worksheet and build a Daily sales report for the brands (Dimproduct). For this reason I have adjusted the Same Month Previous Year Net Revenue calculation a bit (SMPYNetRevenue)

SMPYNetRevenue:=
 IF(ISBLANK([SalesAmount]), 
    BLANK(),
    CALCULATE([NetRevenue], 
       ALLEXCEPT(DimCalendar, DimCalendar[CalendarDate], DimCalendar[Monthselector]),
       FILTER(ALL(DimCalendar), DimCalendar[Year] = MAX(DimCalendar[Year]) -1), 
       FILTER(ALL(DimCalendar), DimCalendar[MonthNumber] = MAX(DimCalendar[MonthNumber]) )
     )
)

Or use the version with PARALLELPERIOD :

SMPYNetRevenue:=
 IF(ISBLANK([SalesAmount]), 
    BLANK(),
    CALCULATE([NetRevenue], 
       ALLEXCEPT(DimCalendar, DimCalendar[CalendarDate], DimCalendar[Monthselector]),
       PARALLELPERIOD(DimCalendar[CalendarDate], -12, month) 
     )
)

And in the screenshot below the Same month Previous Year calculation .

Projected net revenue

Yet another challenge is that the projected Net Revenue depends on the selection of the Calendardate slicer. If a date is selected, the projected net revenue is calculated based on the current working day. If the analysis is done in the pivot on a daily basis (row context) then it's no problem because the projected net revenue expression can calculate the projected net revenue of the month with the rowcontext. In case of the net revenue per brand there is not a date selected. You have to select a date and the projectednetrevenue is properly calculated. For this reason I adjusted the projected net revenue DAX expression to :

ProjNetrevenue:=DIVIDE([MTDNetRevenue] * [SumOfWorkingDaysPerMonth],
                 IF(ISFILTERED(DimCalendar[CalendarDate]),       
                    MAX(DimCalendar[WorkingDayInMonth]),   
                    [CurrentWorkingDay] 
                   )                
                )

Remove the (blank) from the slicer

The last problem I would like to discuss is the (blank) in the Slicer of the monthselector. Because I need the data of the periods for the calculations it's not possible to remove this, but I don't want the (blank) in the selector because it confuses users. 

Too bad, there seems no real solution for this. The solution that I read from the PowerPivotPro forum was the following:

1. It's only possible for vertical slicers (set it to vertical).
2. Reorder the slicer with the Descending.
3. Select "Show item with no data last"
4. Adjust the window such that you can only see Current and previous month

And this is the result:

Not really happy with this solution but it seems that there is no better solution. Suggestions are welcome.

Conclusion

DAX is interesting for analyzing and building dashboards and great reports in Excel PowerBI (v1 en Desktop). Sometimes I lack the possibility to do more customization of the components in Excel.

Greetz,

Hennie