Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
Community Support

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
Not applicable

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
Not applicable

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
Super User
Super User

@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

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.

rishabjain237_0-1693980233625.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors