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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

last week

Hello everybody, i faced a problem for creating a new metrics that displays the compations between this year and the previous year but on the same day of the week and the same week of the year. For example i should compare 'Tuesday, week-33, 2023' with 'Tuesday, week-33, 2022'. How i can create using dax 'Tuesday, week33, 2022'? 

Thank you for everything. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found the solution:

 

Sales LastYear =
IF(
    MOD(YEAR(MAX('Calendar table'[Date]))-1,4) > 0,  -- to check if the previous year is a leap year
    CALCULATE (
        [Tot_sales],
        FILTER (
            ALL('Calendar table'),
            'Calendar table'[Date] <= MAX('Calendar table'[Date]) - 364 && -- if not a leap year subtract 364
            'Calendar table'[Date] >= MIN('Calendar table'[Date]) - 364
        )
    ),
    CALCULATE (
        [Tot_sales],
        FILTER (
            ALL ('Calendar table'),
            'Calendar table'[Date] <= MAX('Calendar table'[Date]) - 363 && -- if is a leap year subtract 363
            'Calendar table'[Date] >= MIN('Calendar table'[Date]) - 363
        )
    )
)

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I found the solution:

 

Sales LastYear =
IF(
    MOD(YEAR(MAX('Calendar table'[Date]))-1,4) > 0,  -- to check if the previous year is a leap year
    CALCULATE (
        [Tot_sales],
        FILTER (
            ALL('Calendar table'),
            'Calendar table'[Date] <= MAX('Calendar table'[Date]) - 364 && -- if not a leap year subtract 364
            'Calendar table'[Date] >= MIN('Calendar table'[Date]) - 364
        )
    ),
    CALCULATE (
        [Tot_sales],
        FILTER (
            ALL ('Calendar table'),
            'Calendar table'[Date] <= MAX('Calendar table'[Date]) - 363 && -- if is a leap year subtract 363
            'Calendar table'[Date] >= MIN('Calendar table'[Date]) - 363
        )
    )
)
Anonymous
Not applicable

Thank you very much for you time, but i faced another kind of problem. I should compare the sales of "tuesday 21th june 2022" with the sale of "tuesday 20th june 2023" so the same day of the week (tuesady) and the same week of the year (25th week of the year over 52). My problem is: how can i obtain "tuesday 21th june 2022"?

You can reference your previous year measure you just created and do the same thing with dateadd().

Last year Sales + one day = CALCULATE([Last year Sales],DATEADD('Date Table'[Date],1,day))
Syk
Super User
Super User

You can use the dateadd function. A quick example that will get your sales from last year...

Last year Sales = CALCULATE(SUM('Table'[Sales]),DATEADD('Date Table'[Date],-1,year))

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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