Week to date and Last year week to date

[MrMP]

Hi,

In my calendar table, I have indicator if date is WTD. If we take example for today 28th of March, that would be 25-28 March (mon-thu).

How to calculate the same time interval for last year? Since today is Thursday, I would have to go back to Thursday last year and return 20-23 March. and those two calculation should stay on same trend chart.

So one line for 4 days in this week and one line for same 4 days in previous year. (yes this cant be achieved with sameperiodlastyear)

Thank you!

[PowerBigginer]

WTDLastYear =
VAR CurrentWeekStart = FIRSTDATE(DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -1, WEEK))
VAR CurrentWeekEnd = LASTDATE(DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -1, WEEK))
RETURN
CALCULATE(
[Measure],
FILTER(
ALL(Calendar),
Calendar[Date] >= CurrentWeekStart &&
Calendar[Date] <= CurrentWeekEnd
)
)

Try this Dax
If it helps please Mark as a solution!

[MrMP]

Thank you for solution. WEEK cannot be argument in LASTDATE so this doesnt work.

[MNedix]

I assume you already have a nice Date table, where you have a WeekStartDate and WeekEndDate columns. Based on the WeekStartDate do a calculated column as below:

Week Rank = RANKX(ALL('Date'),'Date'[WeekStartDate],,ASC,Dense)

Then, create two measures, one for the current week and another one for last year (assuming that [Measure] is what you want to measure - e.g. Sales, Count etc):

This Week = CALCULATE([Measure], FILTER(ALL('Date'),'Date'[Week Rank]=MAX('Date'[Week Rank])))

Last year same Week = CALCULATE([Measure], FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

It should work. If this is the solution you were looking for then pleae mark it as the solution.

[MrMP]

Thank you!

This does not work. It returns good number as total but does not work on a trend. Trend has only WTD dates plotted and has to have same work day last year as trend line.

[MNedix]

Have you tried PARALLELPERIOD ?

[MrMP]

Yep. Doesnt work and cant be plotted on a trend.