same period 2 years ago adjusted to the same weekday

[jveroes_rhc]

Hi All,

I've found a calculation that does what I want (and tested) that just return the same day based on the calendar day; ie for (DD/MM/YYYY) 20/02/2022 it returns 20/02/2021.

Calc_TotalProcQty-2y = calculate(SUM(PROCEDURES_FACT[Procedures]),DATEADD(DIM_CALENDAR[Date],-2,year))

In my data, the behaviour for Mo-Fri is different from Sat,Sun, and even between Mo-Fri there are difference, but comparing the same weekday (ie first Monday of Feb) can be comparable. So, using available date and intelligence date functions I was on the quest of writing my measure but, since Im unsing the DIM_CALENDAR[Date] too much, PowerBI inline interpreter/compiler highlighted that: 

A single value for column 'Date' in table 'DIM_CALENDAR' cannot be determined. This happen when a measure formula refers to a column that contains many values withoutspecifiying an aggregation such as min, max, count or sum to get a single result

The code as the alert started looks like:

Calc_TotalProcQty-2yNew = 
CALCULATE(SUM(PROCEDURES_FACT[Total_Procedures_Qty]),
    DATEADD(DIM_CALENDAR[Date],
        DATEDIFF(DIM_CALENDAR[Date],date(year(DIM_CALENDAR[Date])-2,month(DIM_CALENDAR[Date]),DAY(DIM_CALENDAR[Date])),DAY),DAY)

)

Since I wanted to write my idea down, I used excel formulas to replicate the result I wanted:

P20 is a cell containing a date (DD/MM/YYYY: 16/02/2022

Week day: Wednesday (or 3 using ISO 8601 / week starts on Monday)

Same date, 2 years ago: 16/02/2022 or Sunday (not what I wanted, but current DAX returns that)

Excel formula return (below): 19/02/2020

The decision / logic about going fw or backward in selecting the "closes same weekday" is based on ISO 8601 as well. 

=IF(ABS(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2))<4,
DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)+(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2))),

IF(ABS(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2))=4,
IF(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2)<0,
DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)+3),DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)-3)),

IF(ABS(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2))=5,
IF(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2)<0,
DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)+2),DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)-2)),

IF(WEEKDAY(P20,2)-WEEKDAY(DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)),2)<0,
DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)+1),DATE(YEAR(P20)-2,MONTH(P20),DAY(P20)-1))
)
)

Thanks!

[jveroes_rhc]

Hi @amitchandak,

Thanks for your response. I believe you meant to say on the DATEADD formula to be: 

DATEADD(DIM_CALENDAR[Date],-2*364,day)

In any case, should be day because in fact is ALWAYS 728 days, (including yeap years in between). 

If you can update your response so is clear for other community menbers. Also, you hit reply twice, so there is another reply from you with the same content, if you can, also delete it so the solution and post is clean. I will update this reply afterwards. 

I will look the other resources you shared!

Regards,

Jose

[amitchandak · Accepted solution]

@jveroes_rhc , based on what I got, Same week days last year is 364 days before

so you need

Calc_TotalProcQty-2y = calculate(SUM(PROCEDURES_FACT[Procedures]),DATEADD(DIM_CALENDAR[Date],-2*364,year))

Also, check

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

You can also use weekday filter