cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Current Week versus Same Week Last Year

Hello all,

I am trying to create a custom measure in order to get same week from last year. I work with US calendar and every week restarts on Sunday. Also some years have 52 weeks, others have 53.

I am trying to compare current week with same week last year. Eg: Compare Week 3 year 2023 with Week 3 year 2022. Since there is no time intelligence function that can be used in this case (as most of them compares the same period (same days), but not the same weeks (which can have different days), I need to create a custom measure in order to have an accurate result. Also, I do have other breakdowns in the page, beside the year, quarter, month, week.

Does anybody has any ideas for same week last year measure?

Thank you!

1 ACCEPTED SOLUTION
Super User
``````MeasureSameWeekLastYear = //Try this one might help you
VAR CurrentYearWeek = CONCATENATE(MAX('YourTable'[Year]), "-", MAX('YourTable'[WeekNumber]))
VAR PreviousYearWeek = CONCATENATE(MAX('YourTable'[Year]) - 1, "-", MAX('YourTable'[WeekNumber]))
RETURN
CALCULATE(
[YourMeasure],
FILTER(
ALL('YourTable'),
'YourTable'[YearWeek] = PreviousYearWeek
)
)``````

Proud to be a Super User!

3 REPLIES 3
Helper I

Hi! Quick update on this topic. The measure is working - I just have updated a little bit. I have directly used a column from my calendar table in the variables - I did not use "Concatenate".

Super User
``````MeasureSameWeekLastYear = //Try this one might help you
VAR CurrentYearWeek = CONCATENATE(MAX('YourTable'[Year]), "-", MAX('YourTable'[WeekNumber]))
VAR PreviousYearWeek = CONCATENATE(MAX('YourTable'[Year]) - 1, "-", MAX('YourTable'[WeekNumber]))
RETURN
CALCULATE(
[YourMeasure],
FILTER(
ALL('YourTable'),
'YourTable'[YearWeek] = PreviousYearWeek
)
)``````

Proud to be a Super User!

Helper I

Hi,

Thank you for the quick answer! However, this measures seems not to work. I cannot declare the 2 VARs, as it says the sintax is incorrect.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors