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
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.
Hi,
I tried to create a run rate using the above methodology and does not work for me. This is the error i get.
“The value for column ‘Document Date’ in table ‘MDS_YTD’ cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies.”.
I have similary dates being repeated. I am assuming the error is because of this. How do i over this error.
Regards,
Renato.
Hi Renato,
If you can send me the PowerPivot workbook then I’ll happily take a look – difficult to advise without a whole load more details. Drop me an email with a download link, or dropbox etc to alex at purple frog systems dot com.
Alex
Hi Alex,
I sent you an email with the file attached. so you may download the file and review the same.
Regards,
Ren.
Hi Renato
Sorry for the delay in getting back to you. The problem is that you’re adding the calculation as a new calculated field, not as a calculated column. The YearFrac and other calculations need the context of the current row that you’re working with, so they need to be a calculated column.
Move the calculation and it should all work as you expect.
Regards
Alex
Hi Alex,
Any update regarding the file that i sent and error that i am facing with.
Regards,
Ren.
Hi Renato,
It’s on my list of things to look at, but I’m mega busy at the moment doing a system release for a client – I’ll get onto it as soon as I possibly can, I haven’t forgotten!
Alex
Thanks a bunch. This would help me with a similar error that i am facing when i trying to get the experience of a person.
Hi Alex,
It works great. Is there a way instead of we typing in the first date from the list. Where we could use Firstdate Function and use a text function to get MM/DD/. So the measure would be generic.
Regards,
Ren
[First Date] = Firstdate(Sales[date])
[DateValue]=Format([First Date],”MM/DD/”)
=YEARFRAC(
CONCATENATE([Datevalue]
, year(Sales[Date])-1)
,Sales[Date]))
Yeah something like that should work – The First Date and DateValue should be calculated fields as they apply to the whole table.
The only problem you may get is that this relies on the first date in the table being the year start date – whether this is ok will depend on the density of your data, will you always have every date populated with data?
The fiscal year for us starts from 10/01/, however we get some predated enteries. Thats why i was looking for something where i need not change the date every year.
Also with the above the measures, i am getting a run rate till the last date in my data table. How could i project the data for the future
That won’t work for you then, as you still want the RunRate to be based on your actual fiscal year. You’d need to add a calculated column to the data to artificially move predated data forward to the 1st date of the fiscal year, and then base the RunRate off the calculated date. then your year end date doesn’t change.
RunRate is very different to projection – different concepts entirely. RunRate projects the year end total based on existing data, it doesn’t project a daily/monthly value for future dates. You can however use similar calculations (YearFrac) to work out the portion of the year left, and reduce the RunRate figure accordingly. You’d need to add a date table though to cater for dates that don’t exist in your existing data.
Hi Alex ,
The run rate above work fine when you have a look at the total sales. How could i calculate run rate for a person using the same data which has the sellers name.
Regards,
Renato
Any suggestion how could we calculate TOTAL YTD for a person ?