locked
Dumb question on PArallel Period RRS feed

  • Question

  • This is rather embarasing but I am just starting to pick up powerpivot and time is driving me mad.

    Table FactDailySales has a column SalesExVAT

    Table DimDate has a column CalendarDate (formatted as a date). Now DimDate is joined to all tables on CalendarDateKey.

    So in the powerpivot tab for FactDailySales I want to add a calculated column for this time last year.

    Thinking about it it means finding the same row but last year for the same outlet and product

    Basically

    FactDailySales has columns

    Outlet

    CalendarDateKey

    ProductKey

    SalesExVAT

    And I want a new column or calcyulated measure to show last years value for the same selection?

    This should be easy peasy but is driving me nuts. Oh and I have googled but none of the suggestions work?*!

    Best one seemd to be under the col for salesExVAT drop the calculation

    Measure 2:=CALCULATE (FactDailySales[SalesExVAT],DATEADD(DimDates[CalendarDate],-1,year))

    Semantic Error

    The value for column 'SalesExVAT' in table FactDailySales cannot be determined in the current context and then a bit about circular references.

    Argh

    Any help or pointers gratefully received.

    Tuesday, December 24, 2013 2:14 PM

Answers

  • Hello Ells,

    Does the following DAX expression work for you?

    =CALCULATE(SUM(FactDailySales[SalesExVAT]), PREVIOUSYEAR('DimDates'[CalendarDateKey]))
    

    Here are some good articles regarding Time Intelligence functions in DAX for your reference, please see:
    http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx
    http://www.powerpivotblog.nl/get-the-ytd-of-same-period-last-year-using-dax/

    Hope this helps.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, December 31, 2013 5:56 AM
  • when using CALCULATE you need to specify an expression that evaluates to a numeric value

    as Elvis already posted, SUM() is the way to go here
    though, PREVIOUSYEAR() will give you the value of the whole previous year but not what you would expect from DATEADD())

    instead you could simply use SAMEPERIODLASTYEAR:

    SumSA_LY:=CALCULATE(SUM('Internet Sales'[Sales Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

    or of course your DATEADD()-version

    SumSA_LY:=CALCULATE(SUM('Internet Sales'[Sales Amount]), DATEADD('Date'[Date], -1, year))

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Elvis Long Monday, January 6, 2014 1:00 PM
    Thursday, January 2, 2014 11:19 AM
    Answerer

All replies

  • still having problems with this and its coming up with an error talking about memory and 32 bit. I thought I had 64 bit installed?

    Tuesday, December 24, 2013 3:50 PM
  • Any suggestions for Ells?

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, December 29, 2013 7:13 AM
  • I see 2 things in your measure that you could try fixing.

    1. No "naked" columns are allowed in the first argument of the CALCULATE().  The column reference must be wrapped in some type of aggregate function like SUM(), AVERAGE(), MIN(), MAX(), etc.

    2. DATEADD() is doing straight date math and giving you the date from one year ago.  This will result in the sum of sales for a specific date one year ago.  It sounds like you want the sum of sales for all of the previous year.  Luckily there is a function that does this for you. Instead of DATEADD(), you should use PARALLELPERIOD().  The syntax is similar but it gives you the entire period (day, month, year) referenced as opposed to just a specific date.

    Monday, December 30, 2013 5:17 PM
    Answerer
  • Hello Ells,

    Does the following DAX expression work for you?

    =CALCULATE(SUM(FactDailySales[SalesExVAT]), PREVIOUSYEAR('DimDates'[CalendarDateKey]))
    

    Here are some good articles regarding Time Intelligence functions in DAX for your reference, please see:
    http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx
    http://www.powerpivotblog.nl/get-the-ytd-of-same-period-last-year-using-dax/

    Hope this helps.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, December 31, 2013 5:56 AM
  • when using CALCULATE you need to specify an expression that evaluates to a numeric value

    as Elvis already posted, SUM() is the way to go here
    though, PREVIOUSYEAR() will give you the value of the whole previous year but not what you would expect from DATEADD())

    instead you could simply use SAMEPERIODLASTYEAR:

    SumSA_LY:=CALCULATE(SUM('Internet Sales'[Sales Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

    or of course your DATEADD()-version

    SumSA_LY:=CALCULATE(SUM('Internet Sales'[Sales Amount]), DATEADD('Date'[Date], -1, year))

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Elvis Long Monday, January 6, 2014 1:00 PM
    Thursday, January 2, 2014 11:19 AM
    Answerer