same day of week, same week for previous year calculation with date range selected

[Anonymous]

Hi,

I am facing challenge in calculating same date range last year sales calculation. I have to calculate based on weekday and week selected for previous year. Also it has to be at aggregated level. So suppose I have period 1/4/2022 to 1/6/2022 selected, then I have to calculate data for 1/5/2021 to 1/7/2021 as the day of week are Tuesday, Wednesday, Thursday and week is 1.

I have sales, month, week, day of week, year, date as the fields available. I want to show total aggregated numbers for last year and current year.

Thanks for the support.

Garima 

@amitchandak @tamerj1 

[v-zhangti]

Hi, @Anonymous 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks! This works but just in case we have a previous year date available..in case there isnt, I am getting below error, function DATEADD expects a contiguous sleection when the date column is not unique, has gaps or it contains time portion.

@amitchandak 

[Anonymous]

Thanks for the response.

Here I am not calculating a single week or day. Its a range of weeks and days. When I am using the first logic, its giving me blanks.

[amitchandak · Accepted solution]

@Anonymous , same weekday last year is 364 days behind

example

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

week behind

week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAY))

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))

LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

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])))

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[rishabjain237]

Hey amit. Week behind is a great method to use but can I get something more dynamic. When I apply a fiter..... the filter.......'week behind' should be a week behind so that it can display values.