Powerpivot Contiguous Date ERROR

# Powerpivot Contiguous Date ERROR

• Wednesday, January 09, 2013 12:05 AM

I have a powerpivot that I created a few days ago and today i get a Contigous date error.  My formula is below.  What i want to do is calculate the month to date sales for the current date and compare to the same time last year  ie Sales from   Jan 1-8, 2013  VS Jan 1-8, 2012.  It was working fine however, it think the error has something to do with Jan 8, 2012 falling on a weekend last year (our transactions only take place Mon-Fri).   I use -366 days because the Prior year wasnt matching up to our data when i used -365 days.  I have a similar report with a similar formula that I use to track YTD sales except use TOTALYTD instead of TOTALMTD.

CALCULATE(TOTALMTD([CY MTD Sales],

DATESBETWEEN(dimdate[datekey],

)) )

### All Replies

• Wednesday, January 09, 2013 1:03 PM

You forgot to provide a description of the error.

What error do you get, and most importantly, what is your question? :-)

As a side note: what is wrong with DATEADD(dimdate[datekey],-1,YEAR)?

The Data Specialist (Blog)

• Wednesday, January 09, 2013 4:13 PM

 The error i get is below.  I tried -1 YEAR and the dates werent marrying up to what i wanted ERROR - CALCULATION ABORTED: Calculation   error in measure 'Sales'[YAGO MTD Sales]: Function 'DATEADD' only works with   contiguous date selections.
• Wednesday, January 09, 2013 6:29 PM

Similar question on a thread a few months back.  Check it out here.

Gerhard Brueckl's post at the bottom of that thread should help you.  To summarize here, his formula used SUMX at the day level to roll each individual date back 364 days.

`SA PY:=SUMX(VALUES(DimDate[FullDateAlternateKey])             ,CALCULATE([SA], DATEADD(DimDate[FullDateAlternateKey], -364, DAY))           )`
Hope that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Wednesday, January 09, 2013 7:28 PM

Hi Brent,

This didnt work for me.  For some reason, the measure returns a result thats 80X greater than the value that i am looking for.  Plus it shows me only 1 of my accounts?? My data table has about 3 years of sales in it, so i assume thats part of the problem.  My [datekey] is in the following format MM/DD/YYYY.

John

 Month to Date Dollar Sales Row Labels YAGO MTD Sales CY MTD Sales Diff_ MTD % Chg_ Account A \$                564,886 \$               5,762 -\$559,124 -99.0% Grand   Total \$      17,932,708 \$    369,161 -\$17,563,547 -97.9%
• Wednesday, January 09, 2013 8:04 PM

Please post the updated formula you used.  And need to understand how you're filtering to just current month (slicer, filter, etc).

Also, ensure your date table includes dates back to 1/1/2012 at least.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Wednesday, January 09, 2013 9:16 PM

Hi Brent,

The new formula is below. I am only using slicers and have only selected 2013 on my Yearkey slicer & January on my month slicer.  My dimdate table goes back to 2006.

SUMX(VALUES(DimDate[DateKey]) ,CALCULATE([CY MTD Sales], LASTDATE(DATEADD(DimDate[DateKey], -365, DAY) ) ) )

• Wednesday, January 09, 2013 9:30 PM

SUMX essentially groups by your DateKey and calculates the expression you provide for each date.  Then sums up the results from each date.  So above you are calculating MTD for each day and then adding them all together, duplicating part of the month for each day you have.  That explains the double-counting you were seeing.

Your expression (second param to SUMX - the CALCULATE expression) should just be over a simple measure for SUM of Sales amount.  Like this.

`SumSales:=SUM(Sales[Amount])SumSalesPY:=SUMX(VALUES(DimDate[DateKey])                 ,CALCULATE([SumSales],DATEADD(DimDate[DateKey], -365, DAY))                )`
Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Thursday, January 10, 2013 12:34 AM

Hi Brent,

I used the criteria you mentioned and although the numbers are correct, its not how i want the numbers pulled.

I want to compare Jan 8 2013 YTD to Jan 8 2012 YTD.

The formulas above returns values comparing Jan 8 2013 YTD to Jan 31 2012 YTD

John

• Thursday, January 10, 2013 4:53 AM

Works perfectly for me.

Any chance you can share a sample workbook via SkyDrive?  Or atleast post image of your pivot with filters.  And the values you expect from your data.

• Thursday, January 10, 2013 10:08 AM

• Thursday, January 10, 2013 3:48 PM

Easiest way is to create a new folder, share it (to a specific link or to public), put your file there, and then post the link here.  That way, this is an open forum and anyone can see it and contribute to the solution and/or learn from it.

• Thursday, January 10, 2013 6:22 PM

• Thursday, January 10, 2013 6:56 PM

Can you post the DAX for your YAGO MTD Sales measure that is throwing the error?

• Thursday, January 10, 2013 8:07 PM

This was my original formula

CALCULATE(TOTALMTD([CY MTD Sales],  DATESBETWEEN(dimdate[datekey],

When i change to the following formula, my YAGO MTD Sales = \$17,000,000  (the value should be \$313,000 )

=SUMX(VALUES(DimDate[DateKey])                 ,CALCULATE([CY MTD Sales],DATEADD(DimDate[DateKey], -365, DAY))                )

• Friday, January 11, 2013 1:42 AM

Can you also post your formula for the referenced calculated measure "CY MTD Sales"?

• Friday, January 11, 2013 6:20 AM

The formula is

TOTALMTD(sum(Sales[Amt]),DimDate[DateKey] )

• Friday, January 11, 2013 2:05 PM

By using the TOTALMTD function, you are again introducing the double-counting that I mentioned previously on this thread.