You have followed the time intelligence golden rules to perfection and you think you can handle all time intelligence DAX functions. But unlike the contoso dataset you are working with actual live data from your own production environment and here you have data until the current month not the end of the year. Like this:
We want to compare the YTD from the current year to the YTD of the previous year to the current period last year. First we select the YTD of the current year by selecting the current year in the slicer and using the normal sum. Now to get the YTD of previous year we do a:
=TOTALYTD(sum(Table1[sales]),
DATEADD(datum[Date],-12,MONTH))What happens here? We do the sum of table1[sales] where we use the value of datum[Date] to calculate the sum of sales one year back. What is the result:
This is not what we expected this is the sales of the entire previous year. It is much more. Why? Well one of the golden rules is that you need to have a complete contiguous time table, that means that in my seperate time table we have loaded dates until the end of the current year. And what does TOTALYTD do? According to BOL : “Evaluates the year-to-date value of the expression in the current context.” The current context is “2010” and the lastdate of our time table is 12/31/2010. Not the 6/1/2010 that is the actual last date we had data.
So here we are in a deadlock between the time intellegence golden rules and what we want. When we would use the date from the fact table it would give us the correct last date but the time intellegence functions wouldn’t return results because we wouldn’t use our seperate related time table (read here why).
So what can we do? Well we could make the formula ourself. In our case we want the sum of sales from the same period last year, that means from 1/1/2009 to 6/1/2009. We can use the DAX function DATESBETWEEN to create a custom date range. We want to get the range of datum[Date] values from the first day of the previous year:
FIRSTDATE(DATEADD(datum[Date],-12,MONTH))
We use the datum[Date] column here because we know this is a coninues data range and it would always contain 1/1. Using dateadd we go back 12 months and firstdate makes sure we get the first value from this range. To select the date we want the calculation to end we use:
LASTDATE(DATEADD(Table1[Date],-12,MONTH))
Here we use the lastdate of the fact table to determine the same date one year back, in our case 6/1/2009.
The total formula would look like:
=CALCULATE(sum(Table1[sales]),
DATESBETWEEN(datum[Date],
FIRSTDATE(DATEADD(datum[Date],-12,MONTH)),
LASTDATE(DATEADD(Table1[Date],-12,MONTH))
))PowerPivot DAX will calculate the sum of Table1[sales] from a date range between 1/1/2009 and 6/1/2009.
Which gives us the result we want:
I often use these measures:
[SalesYTD] = TOTALYTD(SUM(Table1[sales]),Date[Date])
[SalesYTDLastYear] = ([SalesYTD])(SAMEPERIODLASTYEAR(Date[Date]))
Do these measures suffer from the same behaviour? If so, this is something you should fix in the next version :-). This is a tough issue to explain to the average user.
Hi @Michiel Rozema ,
Yes they behave exactly the same:
TOTALYTD “Evaluates the year-to-date value of the expression in the current context.”
and
SAMEPERIODLASTYEAR: “Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.”
Both use the context to determine their range. I don’t really know if this is a bug, it is clearly in the definition. I think this is a though one.
Hi. This tip is HUGE to me, so thank you. I will attempt to describe what i am using it for and perhaps someone can tell me if there is yet a better way.
I need to get a MTD of the NEXT month(which will not be a complete month) and compare to the same range of dates in the prior years month. this is the formula I used. The thing is, i have two different places to get the DATE from. One is the FACT table (DATA) and the other is from my DATETABLE (POSTEDDATE).
Is there a better way of handling this?
CALCULATE(SUM(Data[volume]),DATESBETWEEN(PostedDate[PostedDate],CALCULATE(DATEADD(firstdate(Data[PostedMonth]),-12,month),NEXTMONTH(PostedDate[PostedDate]),PostedDate[PostedWorkDay]=1,Source[SourceID]=2),CALCULATE(DATEADD(lastdate(Data[PostedMonth]),-12,month),NEXTMONTH(PostedDate[PostedDate]),PostedDate[PostedWorkDay]=1,Source[SourceID]=2)))
Hi Shawn,
I don’t see a different way right now. There might be a different way but is there a reason you want to have a different query ?
Thanks,
Kasper
I have the same issue. I work with a monthly report, I have a pivot table with sales, YTD, MAT, change over previous year and % change previuos year.
So It look more or less like these.
Radetiketter Liters Liters ifjor Endring Endring % YTD MAT
2008 923 172 945 465 -22 293 -2,36% 923 172 923 172
2009 939 544 923 172 16 372 1,77% 939 544 939 544
2010 981 960 939 544 42 416 4,51% 981 960 981 960
2011 697 135 981 960 -284 825 -29,01% 697 135 697 135
januar 61 807 56 796 5 010 8,82% 61 807 986 971
februar 70 080 60 644 9 436 15,56% 131 887 996 407
mars 83 721 70 610 13 111 18,57% 215 608 1 009 518
april 62 558 65 893 -3 335 -5,06% 278 165 1 006 183
mai 97 843 76 553 21 290 27,81% 376 009 1 027 473
juni 95 785 107 525 -11 739 -10,92% 471 794 1 015 734
juli 60 932 70 639 -9 707 -13,74% 532 726 1 006 027
august 80 424 82 145 -1 721 -2,10% 613 150 1 004 306
september 83 985 83 913 71 0,09% 697 135 1 004 377
oktober 77 165 -77 165 -100,00% 697 135 927 212
november 131 932 -131 932 -100,00% 697 135 795 280
desember 98 145 -98 145 -100,00% 697 135 697 135
But look what happend with oktober, november desembre… THEY APPEAR in the report, this should not occur as destroy the purpose of the report (see the results for 2011). I tried your fix but did not work, it creates another colum with the YTD of 2011, but appears from 2010 and down not in 2011.
I guess the only way to fix it is to deleted from the tables dates the months with sales (i.e oktober and so on)… clearly not the best solution. Any help will be highly appreciated.
These are great tips, but is there a way to calculate SamePeriodLastMonth instead of SamePeriodLastYear? At my company, we do month to month revenue differences and variances.
I’ve already tried the following formulas, which return blank values.
=calculate(sum(SalesOrderDetail[linetotal]),PARALLELPERIOD(SalesOrderHeader[Orderdate],-1,month))
–this formula also returns a blank column!
=calculate(sum(SalesOrderDetail[linetotal]),DateADD(SalesOrderHeader[Orderdate],-1,month))
Please assist.
Hi David,
I would recommend checking my time intelligence post:
http://www.kasperonbi.com/powerpivot-ctp3-what-is-new-for-time-intelligence-functions
and create a date column and use that in your functions. That will return values.
Kasper
Hi Kasper,
I tryed to use integrated time intelligence on our tabular DB, unfortunately our calendar is based on 364 days (52 weeks x 7) .
I have a field with “year / week of year / day of week” but I cannot figure out a solution to obtain the translated set just substracting one thousand.
Any suggestion?
Thank you very much
Fabrizio
Hello Kasper,
I am not sure whether or not I understand this correctly. Using your method, it seems a little imperfect.
For example, today is June 4th. And the last date in fact table is “May 28”, which possibly means no data in the last 6 days for this year. However, in the previous year, there are some data between May 28, 2011 to June 4th, 2011. BY your way, seems to me it would not count this 6 days into previous year YTD. Is this true?
Any possiblity to use the function “Today()”, which apprently can not apply into dateadd, but it might mae more sense to YTD concept.
Great Blog!
I tried your ‘final’ formula but can not get it to work. I am a Beginner and just trying to understand DAX.
My formula:
=CALCULATE
(sum(FactActuals[Net Act Sales]),
DATESBETWEEN(DimDate[Datekey],
FIRSTDATE(DATEADD(DimDate[Datekey],-12,MONTH)),
LASTDATE(DATEADD(FactActuals[Period],-12,MONTH))
))
This is the error message:
The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.
Measure group ‘FactTarget’ must have at least one partition defined in Tabular mode.
Any insights what is going wrong?
Me again 😉
Not sure what happened but I slept one night, re-typed the above formula and voila it works. However I have the following problem.
Let’s say I have Sales Data for years 2010, 2011 and 2012. Works like a charm for 2011 and 2012 but when selecting 2010 it shows under Grand Total the Total Sales of all available periods. What can be done to have BLANK/ZERO shown under Grand Total when there is no data for the PY time period?
Thx again for help.
You managed to solve the problem, I have an equal.
My project is to work a dashbord of management of the imputations in a company(society). I worked the KPI.
My maintaining spot is to work a dashbord (a curve of tendences) which illustrates these KPI over 12 weeks;
I cant found a formula haw to get the last 12 weeks from a variable date
please i need ur help kasper
hi,
for more explication, in my table FACT_IMPUTATION , i have the calculated KPI [rate of imputation],i used to get it until the 12 weeks from a current date, or a variable date from a slicer date
Great article!
I think even better is to: put DATEADD() out of FIRSTDATE() / LASTDATE()
I used the formula and it works, but in my first year of the fact table, where there aren’t previous year and any dates, I found the value corrisponding the total sum of all year, instead 0 or blank value. What is going wrong?
Hi! Alice Botteri,
You managed to solve the problem, I have an equal.
how to compare yoy sales in qlik view using set analysis
new to this blog, but i tried using the above and get blank results…
PY Test = CALCULATE(DISTINCTCOUNT(Terms[Employee Number]),DATESBETWEEN(‘Date Dim'[Date],FIRSTDATE(DATEADD(‘Date Dim'[Date],1,YEAR)),LASTDATE(DATEADD(Terms[Actual Termination Date],1,YEAR))))
Any idea where I am going wrong?
How to Calculate Rolling MTD, YTD Average for Week of the Day By Region
I have Table like
Date , Dayof week (Sunday, Monday etc), Region(East, West, Central), CallCount
I want to calulate MTD AVG_CallCount by Dayof the week For that region & YTD AVG_CallCount by Dayof the week For that region
Example , MTD monday Avg for 2/11
02/11/2018 is the 2nd monday of Feb and 7th Monday of the year.
Now I want to calculate AVG call count for all the Monday till 2/11/2018 (including 2/11) for February , 2/5 & 2/11, for MTD
& AVG call count for all the Monday till 2/11/2018 (including 2/11) for 2018 , for YTD.
Date WeekofDay Region CallCount Avg_MTD_Weekoftheday
1/1/2018 Monday Central 100 100
1/1/2018 Monday East 200
1/1/2018 Monday West 300
1/2/2018 Tuesday Central 150
1/2/2018 Tuesday East 250
1/2/2018 Tuesday West 350
1/3/2018 Wednesday Central
1/3/2018 Wednesday East
1/3/2018 Wednesday West
1/4/2018 Thursday Central
1/4/2018 Thrusday East
1/4/2018 Thrusday West
1/5/2018 Friday Central
1/5/2018 Friday East
1/5/2018 Friday West
1/6/2018 Saturday Central
1/6/2018 Saturday East
1/6/2018 Saturday West
1/7/2018 Sunday Central
1/7/2018 Sunday East
1/7/2018 Sunday West
1/8/2018 Monday Central 200 150
1/8/2018 Monday East 100
1/8/2018 Monday West 50
1/9/2018 Tuesday Central 100
1/9/2018 Tuesday East 200 225
1/9/2018 Tuesday West 300