Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Adjustable Same Periodlastyear function?

HI,

 

I am using the function 'Sameperiodlastyear' and I know about 'dateadd'

 

I have a chart that looks like this:

 

However, my issue is that Thanksgiving falls on a different days of the year, so things can get skewed.  I can't compare Nov 28 to Nov 28 for example.  I need to be able to compate Nov 26 to Nov 28.  I know I can use dateadd to move up or down a day or so, but I would like a slicer(or something) that will allow a user to dynamically shift those days so that we are comparing Thanksgiving day to Thanksgiving day.

 

Any ideas out there?   I have some other slicers set up, while summing works great, it starts falling apart when I want to see day to day comparisons.

 

TIA,

MIke

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You could make a disconnected table with GENERATESERIES(-10,10,1), for example and use the Value column from that in a slicer as your # of days to offset.  You can then use EDATE(MIN(Date[Date]), -12) + SELECTEDVALUE(Offset[Value]) in a variable for your min date, do the same for max date and use those two variables in your CALCULATE filters to get the same period last year w/o time intelligence.

     

    Or you could wrap SAMEPERIODLASTYEAR with DATEADD and use the SELECTEDVALUE as the days to offset in DATESADD.

     

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Ok, thank you... let me give it a shot....I don't consider myself a Dax Pro. ğŸ˜…

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

       

      Ok, I created this:

      testvalLY = DATEADD(SAMEPERIODLASTYEAR('SP_SRT chart'[Date]),SELECTEDVALUE(DayOffset[Day]),DAY)
       
      then I created a table called 'DayOffset' with the values -5,-4,-3,-2,-1,0,1,2,3,4,5
       
      Then I created a slicer so I can pick the above numbers.  But the dates are staying the same.  
       
      I dropped in another table to see my day are indeed changing.
      What am I missing?
       
      TIA,
      Mike