Measure for Previous year with custom date table

Hi al, I have a table with this years sales and last year sales on which I want to filter on both Month and Week. However the weeks are based on the ISO weeknumbers and I want to compare ISO week...

Need Help

v-kelly-msft

5 years ago

Hi Anonymous ,

Modify the measure as below:

Sales Last year =
VAR CurrentWeek =
    SELECTEDVALUE ( 'Calendar'[ISO Week Number] )
VAR CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR currentMonth =
    SELECTEDVALUE ( 'Calendar'[MonthNum] )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Calendar'[Year] ) ),
        "You didnt select a year",
        IF (
            ISFILTERED ( 'Calendar'[Year] ),
            IF (
                ISFILTERED ( 'Calendar'[ISO Week Number] )
                    && NOT ( ISFILTERED ( 'Calendar'[MonthNum] ) ),
                CALCULATE (
                    [Sales],
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[ISO Week Number] = CurrentWeek
                            && 'Calendar'[Year] = CurrentYear - 1
                    )
                ),
                IF (
                    ISFILTERED ( 'Calendar'[ISO Week Number] )
                        && ISFILTERED ( 'Calendar'[MonthNum] ),
                    CALCULATE (
                        [Sales],
                        FILTER (
                            ALL ( 'Calendar' ),
                            'Calendar'[ISO Week Number] = CurrentWeek
                                && 'Calendar'[Year] = CurrentYear - 1
                                && 'Calendar'[MonthNum] = currentMonth
                        )
                    ),
                    IF (
                        NOT ( ISFILTERED ( 'Calendar'[ISO Week Number] ) )
                            && ISFILTERED ( 'Calendar'[MonthNum] ),
                        CALCULATE (
                            [Sales],
                            FILTER (
                                ALL ( 'Calendar' ),
                                'Calendar'[MonthNum] = currentMonth
                                    && 'Calendar'[Year] = CurrentYear - 1
                            )
                        ),
                        IF (
                            NOT ( ISFILTERED ( 'Calendar'[ISO Week Number] ) )
                                && NOT ( ISFILTERED ( 'Calendar'[MonthNum] ) ),
                            CALCULATE (
                                [Sales],
                                FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = CurrentYear - 1 )
                            ),
                            BLANK ()
                        )
                    )
                )
            )
        )
    )

And you will see: