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

Introduction

I'm not happy with the solution presented in my former post about Daily sales report because the current month is determined with the NOW() function. Rob Collie would say that the every time you look at a PowerPivot workbook you think: "that can be improved". The NOW() function calculates the current date like the GETDATE() function in SQL. This solution has some flaws, because when a user comes back months later and he or she wants to use this workbook again it won't work because the data is from months earlier. So, we have to think about a more robust solution.

When searching the web I was looking at the blogpost of Kasper de Jonge: "Use PowerPivot DAX to automatically report on the last month that has data" and he exactly covers this problem. In this blogpost I'll use this as a base for my solution.

MonthSelector

First I decided to add a monthselector slicer to my workbook. I changed the following code (the one with the NOW() function). I created this monthselector as calculated column in the Calendar dimension. This is the initial monthselector Dax expression.

=IF(
  MONTH(Calendar[CalendarDate]) = MONTH(NOW()) && 
  YEAR(Calendar[CalendarDate]) = YEAR(NOW()), 
  "Current Month",
  IF(
   MONTH(Calendar[CalendarDate]) = MONTH(EDATE(NOW(),-1)) && 
   YEAR(Calendar[CalendarDate]) = YEAR(EDATE(NOW(),-1)), 
   "Previous Month"
  )      
)

This is the final DAX expression:

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

I had to add a 'trick' to get it working. I added a RELATED() function in the Facttable to get the CalendarDate column. If I can remove this in the future I'll let you know..

And this is the result with the slicer in the PowerPivot.

Other improvements

Now let's take a look at the estimation DAX expression. As you may have read in my former blogpost, the estimation is the estimated sales for the rest of the month. This is now based on the Month to Date NetRevenue. This is also changed.

This is the DAX expression to determine the estimated sales amounts. As you can see the logic to determine the workingday - based on the a normal day in the calendar dimension- is removed. This automatically done by the PowerPivot engine. So, this is simplification with my initial solution.

SalesEstimation2:=CALCULATE(
                           DIVIDE([SalesAmountMTD] * [SUMWorkingDaysInMonth], 
                           MAX(Calendar[WorkingDayInMonth])
                                 )
                           ) 

The expression of the Month to Date Sales Amount :

SalesAmountMTD:=IF(ISBLANK([SalesAmount]), 
                  BLANK(),
                  Calculate([SalesAmount], DATESMTD(Calendar[CalendarDate])))   
 

This is the DAX expression of the SumWorkingDaysInMonth:

       
SUMWorkingDaysInMonth:=CALCULATE([NumOfWorkingDays], ALL(Calendar[CalendarDate]))
 

NumOfWorkingDays

NumOfWorkingDays:=CALCULATE([NumOfDays], Calendar[WorkingDay] = "Y")

This is the result for the previous month

And this is the result for the current month:

Conclusion

Everytime you work with DAX, you learn better ways with DAX to build your solutions. I'm working now a while with DAX expressions and every time you learn new tricks.

In this blogpost I've used a slicer based on the data in the fact and I've simplified my solution of the part I blogpost.

Greetz,
Hennie