cancel
Showing results 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

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

1 ACCEPTED SOLUTION
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])))

5 REPLIES 5
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.

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.

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

Frequent Visitor

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.

Announcements

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.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

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
Top Kudoed Authors