none
DAX - Time Intelligence - Previous Year, Same week, Same day of week RRS feed

  • Question

  • Hi,

    At a client we have a Tabular model in place having a Sales fact table and Date dimension table.

    The client wants to know the sales quantity of the following period:[Previous Year] + [Same Week] + [Same Day Of Week].

    The following measure works as expected when the "Date" attribute is selected in the context, but when "Month" or "Year" is selected as the context then you get an error that 'DimensionDate'[Date] can't be set with multiple values (returned by "VALUES").

    MeasureSalesPY:=
    SUMX
    (
     FILTER
     (
      ALL('DimensionDate'[Date]);
      'DimensionDate'[Date] = VALUES('FactSales'[PreviousYearSameWeekSameDayOfWeek])
     );
     [MeasureSales]
    )

    The attribute 'FactSales'[PreviousYearSameWeekSameDayOfWeek] contains a calculated value of the current date in the previous year, same week, same day of the week.

    How can this issue (you want to get the sales of the previous year on "Date" level, and want to aggregate it on "Month" or "Year" level) be solved?

    Thank you in advance.

    Regards,

    Johan Machielse

    Sunday, January 8, 2017 7:27 PM

All replies

  • Hi Johan, 

    According to your description, you are trying to get the sales quantity for the same day of  last year, right?

    You can just use SAMEPERIODLASTYEAR or DATEADD function to get the sales quantity. See my below sample DAX:

    MeasureSales Same PeriodLast Year:=CALCULATE([MeasureSales],SAMEPERIODLASTYEAR( 'Date'[Date])) 

    MeasureSales Same PeriodLast Year:=CALCULATE([MeasureSales], DATEADD ( 'Date'[Date], -1, YEAR ) )

     

    As I test in my lab, it works properly for me.

    Internet Sales same day last year:=CALCULATE([Internet Total Sales],DATEADD ( 'Date'[Date], -1, YEAR ))


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 9, 2017 9:21 AM
    Moderator
  • Hi Wilson,

    Thank you for your reply, but this is not what I really meant: a date in the current year is part of a particular week and day of that week, and I want to have a date in the previous year in the same week and same day of week.

    Example
    2017-01-10 (Year = 2017, WeekNr = 2, DayOfWeekNr = 2  (Tuesday))

    To get the date in the previous year with the same WeekNr and same DayOfWeekNr, you will get the following date:
    2016-01-12 (Year = 2016, WeekNr = 2, DayOfWeekNr = 2  (Tuesday))

    So, it's not that easy to just get the same date, one year back by using SAMEPERIODLASTYEAR or DATEADD!

    So, in my fact I already did the determination of the same date in the previous year ('FactSales'[PreviousYearSameWeekSameDayOfWeek])). I just want to get the sales for that determined date and sum them when I aggregate on Month or Year level.

    Regards,

    Johan Machielse

    Tuesday, January 10, 2017 3:26 PM
  • Hi Johan,

    If you have already got that day and the measure [MeasureSales] is a sum of sales quantity, you can just replace DATEADD ( 'Date'[Date], -1, YEAR ) to  'FactSales'[PreviousYearSameWeekSameDayOfWeek] .


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, January 17, 2017 11:59 AM
    Wednesday, January 11, 2017 1:22 AM
    Moderator
  • If you subtract 364 Days you will exactly find the day you need. 

    DATEADD ( 'Date'[Date], -364, day ) 


    cemuney

    • Proposed as answer by Cem Uney Monday, October 30, 2017 2:08 PM
    Monday, October 30, 2017 2:08 PM
  • 364 days doesn't always work because of leap years. 52 weeks, however, has always been correct for me, and using weeks allows you to compare x2 or x4 for longer cycles.
    Friday, November 3, 2017 9:34 PM
  • Hi Johan, I am just feeling your same pain reporting by granular day for the same week last year.

    I tried witha calucalted column using an integer [YearWeekDay] yyyywwdd where dd is the day of the week 1-7 then simply subtracting 10,000 to get the exact same day of the week in the same week number last year.

    I too agree that Sameperiodlastyear nor dateadd(year) work as this is date -365 days, nor week -52 as many organisations have a week 53 some even 54.

    So -10,000 works but cannot aggregate at a higher granular level (week.month,year). So how did you resolve your higher granular aggregation problem in the end ?

    LY Sales (sameWeeksameDayLY) = CALCULATE([Actual Sales], Filter(ALL(MasterCalendar),MasterCalendar[YearWeekDay]=max(MasterCalendar[YearWeekDay]) -10000 ) )



    • Edited by SK_DavidMoss Wednesday, January 3, 2018 5:45 PM edited yyyymmdd to yyyywwdd
    Wednesday, January 3, 2018 1:04 PM