Powerpivot: year to date vs year to date previous year

Question

I am using excel 2013 powerpivot which I have linked to a sql query. In this query I have all sales data from previous years and it updates itself with the current sales. I want to make a pivottable showing year to date sales from this year compared to the same period last year. So for example from Jan.1st 2015 until July 10th of 2015 compared to Jan.1st 2014 until July 10th 2014. I linked my sales data table with a calendar table. But whatever I try, parallelperiod, sameperiodlastyear, totalytd, it always shows me the correct data for this year, but the full year sales of last year. Can anyone recommend me what to try?

thanks,

Frank

Answer 1

The TOTALYTD (and other time intelligence functions) will look for the highest date in whichever context you give it, so when you try to tell it to go back 12 months from now it thinks "OK, I'm in 2014" I'll get ALL the data from 2014 and calculate a TOTALYTD. So you have to ignore the built-in functions and build your own:

  =CALCULATE(sum(Table1[sales]),DATESBETWEEN(DateDim[Date], FIRSTDATE(DATEADD(DateDim[Date],-12,MONTH)), LASTDATE(DATEADD(Table1[Date],-12,MONTH))))

So long as you have a slicer or a field on your table that picks up the year, then that should work.

Edit: Having tested with a data sample, again DAX is trying to be too clever and because I have told it to go back 12 MONTHS, it assumes I want ALL data from the month context which is including everything from July last year. Going back 365 days fixes this (as long as it's not a problem for leap years).

  =CALCULATE(sum(Table1[sales]),DATESBETWEEN(DateDim[Date], FIRSTDATE(DATEADD(DateDim[Date],-12,MONTH)), LASTDATE(DATEADD(Table1[Date],-365,DAY))))

Answer 2

I have tried your way, but it does not yet give me the correct answer. Let me show you what I have right now.

For current year sale I have the following Dax formula

=TOTALYTD(sum(Omzetgegevens[NettoOmzet]); Kalender[Calender date])

For the previous year sale I have:

=CALCULATE((SUM(Omzetgegevens[NettoOmzet])); SAMEPERIODLASTYEAR(DATESYTD(Kalender[Calender date])))

To test your solution I have called "test ytd":

=CALCULATE(SUM(Omzetgegevens[NettoOmzet]); DATESBETWEEN(Kalender[Calender date]; FIRSTDATE(DATEADD(Kalender[Calender date];-12; MONTH)); LASTDATE(DATEADD(Kalender[Calender date]; -365; DAY))))

If I run the pivottable now, the result I get is:

    2015        
    current year ytd    last year ytd   test ytd
januari          28.912          34.487         34.487
februari     50.301          66.003         31.516
maart            73.362          92.647         26.644
april            99.561          117.853    25.205
mei         128.021          149.108    31.255
juni            149.706          174.076    24.968
juli            158.297          205.438    31.362
augustus    158.297          231.693    26.255
september   158.297          254.534    22.841
oktober         158.297          282.484    27.951
november    158.297          303.808    21.324
december    158.297          313.208      9.400
Total   158.297          313.208    313.208

What I would like to see is the following. As this report was run on the 14th of july 2015. I want to see all sales for 2015 until this date and all the sales for 2014 until july 14th 2014.

If it is not possible to see the months, I am also fine with only a total number for current ytd and last year ytd

2015        
    current year ytd    last year ytd   
januari          28.912          34.487         
februari     50.301          66.003         
maart            73.362          92.647         
april            99.561          117.853    
mei         128.021          149.108    
juni            149.706          174.076    
juli            158.297          175.312(so not full month of july in 2014)
Total           158.297          175.312    

Answer 3

Recently had a similar issue. Current context confuse a lot and did not return the result we think should be returned. Play with DATEADD to go back and forth in combination with any of the date/time function. You will get the result and the context will also be clear.