August 20, 2010
In my previous post I explained how to create a calculated MDX member that projects full year data (sales etc.) based on existing year to date data.
In this post I’ll be doing exactly the same but in DAX, the new expression language used to enhance PowerPivot data. As it’s the same desired outcome, I’m not going to repeat the background, you’ll have to look at my previous post for that.
The expressions below assume that you have a single table ‘Sales’ with a [Date] column and an [Internet Sales Amount] column.
Step 1 – What are our total sales so far this year?
We use the TOTALYTD function to work this out for us
=TOTALYTD(SUM(Sales[Internet Sales Amount])
,Sales[Date], all(Sales))
The first parameter is the expression we want to calculate, i.e. the sum of internet sales.
The second parameter specifies the date we’re calculating up to.
The third parameter is one that catches a lot of people out. We have to tell DAX the context of the date. As it stands the expression can only see the data in the selected row, by specifing all(Sales) in the filter we expand the expression to be able to look at all of the data.
Step 2 – How far are we though the year?
It’s here where DAX really shows an improvement in the functions available over and above what’s available in MDX. There’s a YEARFRAC function which calculates how far we are through the year.
=YEARFRAC(
CONCATENATE("01/01/"
,year(Sales[Date]))
,Sales[Date]))
The first parameter is the start date, i.e. the 1st January. We have to build this using the year of the selected row to ensure we get the right year.
The second parameter is the date of the record we’re looking at, Sales[Date].
Step 3 – Project the value to the end of the year
We combine the two values by simply dividing the YTD figure by how far we are through the year
=TOTALYTD(SUM(Sales[Internet Sales Amount])
,Sales[Date], all(Sales))
/ YEARFRAC(
CONCATENATE("01/01/"
, year(Sales[Date]))
,Sales[Date]))
CORRECTION
Thanks to Sandeep Nallabelli in the comments below or pointing out a bug in this, YearFrac should actually be calculated from 31st Dec instead of 1st Jan. Updated calculation should be:
=TOTALYTD(SUM(Sales[Internet Sales Amount])
,Sales[Date], all(Sales))
/ YEARFRAC(
CONCATENATE("31/12/"
, year(Sales[Date])-1)
,Sales[Date]))
Also, it is worth adding the YearFrac calculation as a calculated column in your date table so that you can use it in measures.
This chart shows how the full year run rate is adjusted throughout the year as the cumulative sales to date grows. At the start of the year it’s quite volatile, but from February it settles down with an acurate projection.
And it really is as easy as that.
Frog-Blog Out
Posted in 
Tags: 10 comments on “Calculating Run Rate in DAX for PowerPivot”
Leave a Reply
I'm Alex Whittles.
I specialise in designing and implementing SQL Server business intelligence solutions,
and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.
Alex…Thank you for the article….It really got me started on Run Rates in DAX. In the article the run rate is calculated on yearly bais…as we are looking for year…can you help me let me know, how can i calculate on to daily run rate…instead of YearFrac..what can i use to get the day basis.
Hi Sandeep. How do you mean calculate on a daily basis? YearFrac calculates how far you are through the current year, whether you’re looking at a month or a day, it will work out automatically whether to calculate it in 12ths or 365ths.
So you can use this function to produce daily, weekly and monthly run rates. It will automatically decide what needs to be done depending on what you select as the date.
Does this make sense?
Thank you for the clarification Alex. I am sorry for my question being wrong. I was actually implementing your calculation but i found that i cannot do
=YEARFRAC(CONCATENATE(“01/01/”,year(Sales[Date])),Sales[Date]) as a measure. I tried different things. This comes as a calculated column rather than a measure. Is there a way that i can implement the above as a measure. Or should i sum the columns Total YTD and YearFrac and divided them as you have shown above. I appreciate your input and thanks in advance
Yeah you’d need to add the YearFrac part of the formula as a calculated column in the date table (called something like YrFrac), then in the measure formula you could multiply the YTD sales by (1/max(‘Date’[YrFrac]))
Thanks Alex. You have been a great help.
Alex…Thank you for the great help earlier. I have one question though. The formulae went through and got me all the values except for few. I am getting #NUM! value for few fields. For example when i put Calendar Hierarchy of date and DailyRunRates I see #NUM! for Jan 1 2011. I am miffed up since i got all the values for everything else. Can you help me on this issue.
I found out the Issue. The issue is with YearFrac is as below:
=YEARFRAC(CONCATENATE(“01/01/”,YEAR(‘Demand Planning’[Activity Date])),’Demand Planning’[Activity Date]). So we have the Activity Date as ’01/01/2011′ and ’01/01/2011′. As we all know Yearfrac gives us the
date difference between two dates. If there is no date difference We are getting a 0 value. Hence the Error. Can you suggest any work arounds?
Thanks,
Sandeep
That’s a very interesting point – well spotted.
1/1/2012 is treated by YearFrac as 0% through the year (and xxx/0=Error), i.e. it effectively treats it as 1/1/2012 00:00, not 1/1/2012 23:59. When we show 1/1/2012 on a chart it is usually accepted that it refers to the end of day value. We therefore need to add an extra day to the YearFrac calculations. We can do this by changing the first parameter to 31st December of the previous year instead of 1st Jan of the current year.
=YEARFRAC(
CONCATENATE(“31/12/”,
year(‘Date’[Date])-1),
‘DATE’[Date])
Thanks for the feedback, hope this sorts out the problem!
Alex
Hello Alex,
thank you for this. It’s working perfectly. However, I was wondering, is it possible to change the x-axis to display only Months instead of random dates from your Sales[Date] field?
Thank you in advance.
The DAX calculation will work on any date that it encounters. So all you need to do is change the x-axis on the chart and it should all work for you. If you’ve set up a date table then that should have a Month (or Year-Month) column in it, just use that as the x-axis on the chart.