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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Elina_Mirin
Helper I
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
mh2587
Super User
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
    )
)

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



View solution in original post

3 REPLIES 3
Elina_Mirin
Helper I
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". 

 

Thanks for your help!

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

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.