Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 e...
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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