Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Powerpivot Contiguous Date ERROR

Unanswered Powerpivot Contiguous Date ERROR

  • Wednesday, January 09, 2013 12:05 AM
     
     
    Sign In to Vote
    0

    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],

                                      FIRSTDATE(DATEADD(dimdate[datekey],-366,DAY)),

                                      LASTDATE(DATEADD(sales[Date],-366,DAY))

                      )) )

     

All Replies

  • Wednesday, January 09, 2013 1:03 PM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    1

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


     
  • Wednesday, January 09, 2013 7:28 PM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


     
  • Wednesday, January 09, 2013 9:16 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    1

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



     
  • Thursday, January 10, 2013 12:34 AM
     
     
    Sign In to Vote
    0

    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

    Thanks for your help

    John

     
  • Thursday, January 10, 2013 4:53 AM
     
     
    Sign In to Vote
    0

    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. 

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

     
  • Thursday, January 10, 2013 10:08 AM
     
     
    Sign In to Vote
    0
    Ill upload to skydrive..contact info?
     
  • Thursday, January 10, 2013 3:48 PM
     
     
    Sign In to Vote
    0
    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. 

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

     
  • Thursday, January 10, 2013 6:22 PM
     
     
    Sign In to Vote
    0
     
  • Thursday, January 10, 2013 6:56 PM
     
     
    Sign In to Vote
    0
    Can you post the DAX for your YAGO MTD Sales measure that is throwing the error?

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

     
  • Thursday, January 10, 2013 8:07 PM
     
     
    Sign In to Vote
    0

    This was my original formula

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

                                      FIRSTDATE(DATEADD(dimdate[datekey],-366,DAY)),

                                      LASTDATE(DATEADD(sales[Date],-366,DAY)) )) )

    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
     
     
    Sign In to Vote
    1
    Can you also post your formula for the referenced calculated measure "CY MTD Sales"?

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

     
  • Friday, January 11, 2013 6:20 AM
     
     
    Sign In to Vote
    0

    The formula is

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

     
  • Friday, January 11, 2013 2:05 PM
     
     
    Sign In to Vote
    1

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

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com