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

DAX: The very long message "The semantic error or perhaps this error or this error.."

One day, I was working with a DAX expression in PowerPivot (Excel 2013) and I got the following error:

"Semantic error: The value for column 'Date' in Table 'dimCalendar' cannot be determined in the current context. Check that all columns in referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation -- such as sum, average, or count-- on that column. The column does not have a single value, it has many values, one for each row in the table, and now row has been specified."

Okay.. I prefer shorter messages normally and this error message popups in a tooltip and this tooltip disappears after a couple seconds. You have to move the cursor back and again and then you have a couple of seconds to read the tooltip again, before it disappears again. Well, I have written it all out in this blogpost and so you can read it at ease with a cup of coffee.

This was this the DAX expression that generated the error:

SumNetRevenue:=IF(MONTH(DimCalendar[Date]) = MONTH(NOW());
  SUM(FactCurrentMonthSales[NetRevenue]))
 

Now, the second part of the error is the interesting part and that said that there are different granularities in the expression. The SUM is on filtercontext but, the DimCalendar[Date] is on row level. I solved it with this DAX Expression:

SumNetRevenue:= IF(MONTH(MAX(DimCalendar[Date])) = MONTH(NOW()) ;
                 SUM(FactCurrentMonthSales[NetRevenue]))

Conclusion

Don't use very very long error messages in a tooltip ;-)

Greetz
Hennnie