Calculate Sales for Same Day of the Week LY

[user5341567]

Hello,

I am trying to calculate sales LY that would be equal to the same day of the week LY (i.e., 2/1/2025 vs. 2/3/2024). 

Most solutions I am finding are using SAMEPERIODLASTYEAR, which compares 2/1/2025 vs. 2/1/2024. I also can't use 

DATEADD(dim_date[date], -364, DAY), because it woudn't calculate correctly on leap year.

I have a column in my dim_date table called 'comparisondate', which correctly shows 2/3/2024 for the 2/1/2025 record.

Does anyone know what is the best way to calculate sales for the comparison date, when I have the 'date' field selected in a slicer? I usually am using a date range, instead of just one date, so I can't use SELECTEDVALUE. So when I select 2/1/2025 - 2/28/2025 in the date slicer, I would like to calculate sales for 2/3/2024 - 3/1/2024.

Any responses would be appreciated. Thank you!

[OwenAuger · Accepted solution]

@user5341567 

If you already have comparisondate defined in dim_date, then this is how I would write it:

Sales LY =
CALCULATE (
    [Sales Measure],
    TREATAS ( VALUES ( dim_date[comparisondate] ), dim_date[date] )
)

As long as dim_date is marked as a date table, this will remove any filters on dim_date and apply the values of dim_date[comparisondate] as a filter on dim_date[date].

Does this work for you?

[user5341567]

This worked perfectly! So glad to see this. I appreciate your response!

[Greg_Deckler]

@user5341567 Maybe something like this:

Measure Date LY Comparison =
  VAR __Date = DATE( 2025, 2, 1 )
  VAR __Year = YEAR( __Date )
  VAR __WeekNum = WEEKNUM( __Date )
  VAR __WeekDay = WEEKDAY( __Date )
  VAR __Result = 
    MAXX(
      FILTER( 'Calendar', YEAR( [Date] ) = __Year - 1 && WEEKNUM( [Date] ) = __WeekNum && WEEKDAY( [Date] ) = __WeekDay ),
      [Date]
    )
RETURN
  __Result

[user5341567]

Thank you for taking the time to respons. Unfortunately, as I'm using a date range slicer, it doesn't seem to be calculating correctly. It shows as blank when I have 2/1 - 2/1 in the date slicer. Even though it's a single date, I think it's being thrown off becasue the slicer is a 'between' format: