locked
exact date range comparison RRS feed

  • Question

  • I have payment table like this,

    Date | Store | payment

    01/01/2012 | A | $300 

    01/01/2012 | B | $400 

    01/01/2012 | C | $500 

    01/02/2012 | A | $500

    .

    .

    .

    01/22/2015 | C | $300

    I want to make a report like this,

                                  A                                                           B                           ........     Totals

            Current year | Last Year | Difference         Current year | Last Year | Difference .....     Totals

    Jan

    Feb

    March

    .

    .

    Dec

    However for comparing last year & current year, I only want to take into account the number of days in the current month. For instance, if current year-month sales are only up-to-date until 22 then for previous year, i want to show sales for Jan only until 22.

    I know I can hardcode this for previous year sales  by specifying harcoded dates, but anytime I try using something like calculate(sum(payment), formula to calculate last date in current year and use dateadd() formula move this back by one year) always results in error like "cannot use formula in calculate filter"

    is there any solution to this? 

    Friday, January 23, 2015 8:31 AM

Answers

  • Hi Bellicose,

    If I've correctly understood what you're asking for then this should give you the desired outcome:

    PaymentPriorYearNew:=
    IF(
      HASONEVALUE(DimDate[Year]),
      CALCULATE( 
        [Payment_],
        DATEADD(
          FILTER(
            VALUES(DimDate[DateKey]),
            DimDate[DateKey] <= LASTNONBLANK(ALL(DimDate[DateKey]), [Payment_])
          ), 
          -1, 
          Year
        )
      )    
    )

    Here's what the output looks like for 2015...

    ...And for 2014:

    All that's left is to calculate the difference as you did previously e.g.

    Difference := [Payment_] - [PaymentPriorYearNew]


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, February 17, 2015 12:42 PM

All replies

  • You need the following measures:

    PaymentYTD:=CALCULATE(SUM([Payment]),DATESYTD(Dates[Date],"12/31"))

    Note: Dates[Date] is Date table (Date dimension) and "12/31" is Year End Date, and make sure that you mark your Date table as Date Table in Power Pivot model. If you want to calculate Month to Date, you can modify the function above and use DATESMTD instead of DATESYTD.

    PaymentPriorYear:=CALCULATE([PaymentYTD], SAMEPERIODLASTYEAR(Dates[Date]))

    Difference:= [PaymentYTD] - [PaymentPriorYear]

    HTH!

    Ayad


    Saturday, January 24, 2015 2:28 AM
  • Hi Ayad,

    I tried it, but I got the following,

    PaymentYTD:= (all month are showing total YTD value instead of breaking them down by month)

    PaymentPriorYear:= It gives the error below,

    Monday, January 26, 2015 11:46 PM
  • Can you post both functions? and confirm that your dates in Dates Table are continuous (no gaps).

    If possible, can you post also a sample data?

    Thanks,

    Ayad

    Friday, January 30, 2015 4:17 AM
  • PaymentYTD:=CALCULATE(SUM([Amount]),DATESYTD(Dates[Date],"12/31"))
    PaymentPriorYear:=CALCULATE([PaymentYTD], SAMEPERIODLASTYEAR(Dates[Date]))

    The dates in Dates tables are contiguous, however there are missing dates in payment table (as the store for closed for some of the holidays). 
    Below is sample data, 

    PAYMENT_DATE AMOUNT STORENO PAY_DATEKEY
    01/02/2014 9:51 $359.00 1 01/02/2014
    01/02/2014 10:08 $283.00 1 01/02/2014
    01/02/2014 10:09 $497.00 1 01/02/2014
    01/02/2014 10:23 $494.00 1 01/02/2014
    01/02/2014 10:34 $27.00 1 01/02/2014
    01/02/2014 10:34 $63.00 1 01/02/2014
    01/02/2014 10:56 $453.00 3 01/02/2014
    01/02/2014 10:56 $175.00 3 01/02/2014
    01/02/2014 10:59 $197.00 1 01/02/2014
    01/02/2014 11:00 $145.00 1 01/02/2014
    01/02/2014 11:01 $373.00 3 01/02/2014
    01/02/2014 11:06 $475.00 1 01/02/2014
    01/02/2014 11:10 $413.00 2 01/02/2014
    01/02/2014 11:11 $431.00 2 01/02/2014
    01/02/2014 11:13 $131.00 2 01/02/2014
    01/02/2014 11:16 $34.00 2 01/02/2014
    01/02/2014 11:16 $59.00 2 01/02/2014
    01/02/2014 11:17 $203.00 1 01/02/2014
    01/02/2014 11:19 $80.00 1 01/02/2014
    01/02/2014 11:27 $418.00 1 01/02/2014
    01/02/2014 11:27 $198.00 1 01/02/2014
    01/02/2014 11:28 $354.00 2 01/02/2014
    01/02/2014 11:29 $19.00 2 01/02/2014
    01/02/2014 11:33 $425.00 3 01/02/2014
    01/02/2014 11:34 $296.00 2 01/02/2014
    01/02/2014 11:34 $302.00 1 01/02/2014
    01/02/2014 11:35 $244.00 3 01/02/2014
    01/02/2014 11:35 $13.00 3 01/02/2014
    01/02/2014 11:39 $419.00 2 01/02/2014
    01/02/2014 11:43 $144.00 2 01/02/2014
    01/02/2014 11:43 $206.00 2 01/02/2014
    01/02/2014 11:47 $194.00 3 01/02/2014
    01/02/2014 11:48 $479.00 3 01/02/2014
    01/02/2014 11:53 $20.00 1 01/02/2014
    01/02/2014 12:06 $419.00 1 01/02/2014
    01/02/2014 12:15 $129.00 1 01/02/2014
    Saturday, February 7, 2015 3:33 AM
  • strange - in order to find the error try the following:

    1) Check whether the connection between your payments table and the date table has been successful (wouldn't raise error in some cases): Create a new Pivot table with amount in values area, then drag a date field from your payments table into lines and then a date field from your date table. If there's a 1:1 match, then it's been successful.  

    2) Are you sure that you copied the correct formulas in your last post? As they don't exactly fit the screenshot from your error-message: There your measures was called: Payment [Last Year] while here it says: PaymentPriorYear


    BR, Imke

    Monday, February 9, 2015 8:50 PM
    Answerer
  • I've created couple of different workbooks to test it out, below the formula & error from one book.

    PaymentYTD:=CALCULATE( 
                                       SUM( PAYMENT[AMOUNT] ),
                                       DATESYTD( DimDate[DateKey], "12/31")
                                                  )
    PaymentPriorYear:=CALCULATE( [PaymentYTD],
                                                           SAMEPERIODLASTYEAR( DimDate[DateKey] )
                                                         )
    Diff:=[PaymentYTD] - [PaymentPriorYear]

    The relationship is working fine since, I have several other pivot tables that use SUM( Payment[Amount] ) in the value section, broken down by month, year, date all from dates table.

    I've figured out this formula that does seem to work,

    2015_:=CALCULATE( 
                                       SUM( PAYMENT[AMOUNT] ),

                                       DimDate[Year] = 2015,
                                       KEEPFILTERS( 
                                                                  DimDate[DateKey] < DATE( 2015, MONTH( TODAY()), DAY(TODAY()))
                                                               )
                                      )

    2014_:=CALCULATE(
                                        SUM( PAYMENT[AMOUNT] ),
                                        DimDate[Year] = 2014,
                                        KEEPFILTERS(
                                        DimDate[DateKey] < DATE( 2014, MONTH(TODAY()), DAY(TODAY()) )
                                        )
                                      )

    DIFFERENCE_:=[2015_] - [2014_] 

    But the dates are hard coded, which is what I want to avoid, I want something that can look at dates in current & past year automatically without hard coding the year values.

    
    Tuesday, February 10, 2015 5:55 PM
  • hm, everythings pointing toward non-contingeous dates. But if you've checked that and without access to the workbook it's just some last suggestions:

    2) Try Datesytd without the optional parameter "12/31"

    3) If you get Datesytd working, you could also try Dateadd(Datesytd,Year,-1)

    See: http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx

    Good luck


    BR, Imke

    Tuesday, February 10, 2015 11:09 PM
    Answerer
  • PaymentYTD:=CALCULATE( 
                                       SUM( PAYMENT[AMOUNT] ),
                                       DATESYTD( DimDate[DateKey])
                                                  )

    PaymentPriorYear:=CALCULATE( [PaymentYTD],
                                                           DATEADD( DATESYTD( DimDate[DateKey] ), -1, year)
                                                         )

    Both of these formula's work. The problem is that they are giving me the right value for January but for February they are showing me (jan+feb) total and not just the February sales

    Wednesday, February 11, 2015 12:38 AM
  • Oh, I see - didn't read good enough, sorry.

    It's even easier then:

    Payment_:=SUM( PAYMENT[AMOUNT] )
     

    PaymentPriorYear:=CALCULATE( [Payment_],
                                                           DATEADD(DimDate[DateKey], -1, year)
                                                         )


    BR, Imke


    Wednesday, February 11, 2015 12:24 PM
    Answerer
  • PaymentPriorYear gives the contiguous date needed error!
    Wednesday, February 11, 2015 9:12 PM
  • When does it give the error message? When creating the measure or trying to Pivot it/update the existing pivot?

    Try it with a newly built pivot - these measures work in my example - I can upload a file if you like.


    BR, Imke

    Wednesday, February 11, 2015 9:23 PM
    Answerer
  • PaymentPriorYear gives the contiguous date needed error!

    Assuming that you have no gaps in your date table, this error is most likely due to when the DAX formula is evaluated at the grand total level and there are multiple years - in this scenario there will be non-contiguous dates.

    What happens when you wrap Ayad's or Imke's formulas in an IF function? I've given an example below:

    PaymentPriorYear:=
    
    IF(
      HASONEVALUE(DimDate[Year]))
      CALCULATE(
        [Payment_],
        DATEADD(DimDate[DateKey], -1, year)
      )
    )

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, February 16, 2015 2:47 PM
  • Hi Michael,

    Last formula just gives blanks for PaymentPriorYear, I've created a test file, randomized the payment amount, but have kept the dates in payment_table & dimDate table the way they are... Please see the download link below.

    Test file



    Monday, February 16, 2015 7:27 PM
  • Hi Bellicose,

    I can see what's happening here. When you place the MonthName on rows and Payment_ in values, the filter context on the MonthName values (I.e January, February, etc) spans multiple years. This is why the adapted version of Imke's solution that I presented above returns blank values; HASONEVALUE(DimDate[Year]) would evaluate to false and trigger the portion of the IF function that returns a blank result.

    If we use January as an example, the filter context for this MonthName value includes the years 2010 up to 2015. Since we have multiple years in this context, 'last year' has an ambiguous meaning and that's what triggers the contiguous dates error. The dates in this context would be dates from January 1st to the 31st repeated for each year.

    Here are some options to get the right behaviour:

    a) Place the DimDate[Year] column on the Rows area above the MonthName in the Pivot table. This will repeat the MonthName values for each year and ensure that there is the right filter context for the previous year calculation to work.

    Or

    b) Place the DimDate[Year] column in the Filters area and filter on a year.

    Or

    c) Use the Power Pivot Window to add a Calculated Column that appends the year to the month to create values such as "January 2015" or "Jan 15" and use this on Rows instead of MonthName. You'll need to create an accompanying column that can be used to sort this column's values in the right order i.e a column with a number representation of the month such as 201501 for January 2015.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, February 16, 2015 10:14 PM
  • Here's what happening when I put the year in the rows and only filter current year,

    PaymentPriorYear is showing values for all months, instead of just January and Month to Date days in Feb
    Monday, February 16, 2015 10:37 PM
  • Hi Bellicose,

    If I've correctly understood what you're asking for then this should give you the desired outcome:

    PaymentPriorYearNew:=
    IF(
      HASONEVALUE(DimDate[Year]),
      CALCULATE( 
        [Payment_],
        DATEADD(
          FILTER(
            VALUES(DimDate[DateKey]),
            DimDate[DateKey] <= LASTNONBLANK(ALL(DimDate[DateKey]), [Payment_])
          ), 
          -1, 
          Year
        )
      )    
    )

    Here's what the output looks like for 2015...

    ...And for 2014:

    All that's left is to calculate the difference as you did previously e.g.

    Difference := [Payment_] - [PaymentPriorYearNew]


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, February 17, 2015 12:42 PM
  • JACKPOT!! This is exactly what I was looking for.. I only made one change to the report which was adding another calculated column to dimDate table, IsInCurrentYear=IF( YEAR([DateKey]) = YEAR( TODAY() ),"TRUE","FALSE") , then set it to true in the report file instead of selecting current year using the year filter so I won't need to change it either when next year comes. Thank you all for your help with this.
    Tuesday, February 17, 2015 7:49 PM