Lessons Learned with Power Bi and Dynamic DAX Expressions

Labels: , , , ,
Since its availability, I try to use Power Bi as often as I can. It is so convenient, to build visual to explain data coming from a ton of possible data source. And it's a breeze to share it with clients, or colleagues. However, this post is not an info-commercial about Power Bi, it's about sharing some challenges I got trying to prepare a report and how I fix it.

The Data Source


The context is simple, all transactions are in one table, and I have a second table with a little information related to clients. To that, I personally like to add a calendar table, because it simplifies my life.

Datamodel

For this report, it is very important to but a slicer by Client.

The Goal


I needed to have one report that shows for every customer three different Year To Date (YTD) total. The classic YTO, a YTD but when the beginning of the years is, in fact, when the client started is enrolment, and the last one was a rolling twelve.
It looks pretty simple, and in fact, it's not that complicated. Let's examine each total formula one by one.

Classic Year To Date Total


Before we get started, it's a good practice to reuse Mesure to simplify our formula, and to explicit expression. Let's create a Measure for the Total Sales, that will be used inside our other formulas.

TotalSales = SUM('Sales'[Total])

Now the Year To Date, is simple to add by adding a New Measure and entering the formula:

YDTClassic = TOTALYDT([TotalSales], 'Calendar'[Date])

If you activate the Preview feature of Power Bi, it could be even easier. Look for the button New Quick Measure and select the Total Year To Date, fill up the form and voila!

QuickMeasure

The generated formula looks a bit different because Power Bi managed the error in h expression.

TotalYTD = 
IF(
    ISFILTERED('Calendar'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
    TOTALYDT([TotalSales], 'Calendar'[Date].[Date])
)

Anniversary Year To Date Total


I spent more time then I was expecting on that one. Because in the Online DAX documentation it is said that the formula TOTALYDT accept a third parameter to specify the end of the year. So if I had only one client, with a fix anniversary date (or a fiscal year) this formula will work assuming, the special date is April 30th.
TOTALYDT([TotalSales], 'Calendar'[Date], "04-30")
However, in my case, the ending date changes at with every client. I'm sure right now you are thinking that's easy Frank just set a variable and that it! Well, it won't work. The thing is the formula is expecting a static literal, no variable aloud even if it returns a string.
The workaround looks at first a bit hard, but it's not that complex. We need to write our own YTD formula. Let's look at the code, and I will explain it after.

Anniversary YTD = 
VAR enddingDate =   LASTDATE(Company[EnrolmentDate])
VAR enddingMonth =  MONTH ( enddingDate )
VAR enddingDay =    DAY ( enddingDate )
VAR currentDate =   MAX ( Calendar[Date] )
VAR currentYear =   YEAR ( currentDate )
VAR enddingThisYear =   DATE ( currentYear, enddingMonth, enddingDay )
VAR enddingLastYear =   DATE ( currentYear - 1, enddingMonth, enddingDay )
VAR enddingSelected =   IF ( enddingThisYear < currentDate, enddingThisYear, enddingLastYear )
RETURN
    CALCULATE (
        [TotalSales] ,    
        DATESBETWEEN(Calendar[Date],enddingSelected,currentDate)    
    )
First lines are all variable's declaration. They are not required, but I found it easier to understand when things are very explicit. Since I'm slicing my report by companies putting the LASTDATE is just a way not avoid errors. It should have only one record. Then we extract year, month, and day.
The last variable enddingSelected identify if the anniversary (the end date) is pasted or not in the curent calendar year.
The calculate function is returning the TotalSales between the last anniversary date and today.

Rolling twelve Total


For the last formula, the rolling twelve we will re-use the previous code, but in a simpler way since the end date is always yesterday.

Rolling 12 Total = 
VAR todayDate = TODAY()
VAR todayMonth =    MONTH ( todayDate )
VAR todayDay =  DAY ( todayDate ) 
VAR todayYear = YEAR ( todayDate ) 
VAR enddingLastYear =   DATE ( todayYear - 1, todayMonth, TodayDayVar +1) 
RETURN
    CALCULATE (
        [TotalSales] ,
        DATESBETWEEN( Calendar[Date], enddingLastYear, todayDate)   
    )

Wrap it up


I definitely learned a few things with that Power Bi session, but it turns out to be pretty easy. Again, leave a comment or send me an email if you have any comments or questions I will be very happy to ear from you.


References