Measure for Previous year with custom date table

[BobKoenen]

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 numbers against previous year. 

For your infomation below a representation of the ISO week numbers in the date table.

Date	Normal week	ISO week numer	Month
1-1-2021	1	53	1
2-1-2021	1	53	1
3-1-2020	1	53	1
4-1-2020	1	1	1
5-1-2020	1	1	1

In 2020 it looks like:

date	normal week number	Iso week numer	month
1-1-2020	1	1	1
2-1-2020	1	1	1
3-1-2020	1	1	1
4-1-2020	1	1	1

So if I select the ISO week number 1 want to compare the dates which are on week 1 of last year. 

Fortunatly I found the formula below which does that perfectly

Sales Last year =

VAR CurrentWeek = SELECTEDVALUE( 'Calendar'[ISO Week Number] )

VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year] )

RETURN

CALCULATE( [Sales],

FILTER( ALL( 'Calendar' ),

        'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1))

However this measure does not work if I select a Month. I can understand why but I do not know how to incorporate the normal month numers in this measure. 

Hope you can help me. 

[v-kelly-msft]

Hi  @BobKoenen ,

Based on your description,I cant competely reproduce your senario,could you pls provide some sample data with expected output for a test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[BobKoenen]

Hi Kelly. See the attached file. Hope you can open it. 

The problem is when I select o month or only a year the current measure does not give any values. I want this measure to also work when selecting a month and year and only a year. 

https://koenenenco-my.sharepoint.com/:u:/g/personal/b_kessels_koenenenco_nl/EUfA0ensLQNDhShy7c_lV_cB... 

[v-kelly-msft]

Hi  @BobKoenen ,

After checking,if you have selected a year,then you wont see an error,so modify your measure as below:

Sales Last year = 
VAR CurrentWeek = SELECTEDVALUE( 'Calendar'[ISO Week Number] )
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year] )

RETURN
IF(ISFILTERED('Calendar'[Year]),
CALCULATE( [Sales],
FILTER( ALL( 'Calendar' ),
        'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1)),
        CALCULATE( [Sales],
FILTER( ALL( 'Calendar' ),
        'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] =YEAR(TODAY()) - 1)))+0

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[BobKoenen]

@v-kelly-msft  I am Sorry that i am not clear enough but this measure still does not give me a result when only selecting a month. 

I also want previous year sales when selection only a Monthnum and a Year:   

[v-kelly-msft · Accepted solution]

Hi @BobKoenen ,

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@BobKoenen , Check if this can work

Sales Last year =
VAR CurrentWeek = maxx(allselected('Calendar'[), 'Calendar'[ISO Week Number] )
VAR CurrentYear = maxx(allselected('Calendar'[), 'Calendar'[Year] )

RETURN
CALCULATE( [Sales],
FILTER( ALL( 'Calendar' ),
'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1))

[BobKoenen]

@amitchandak 

Unfortunatly not. Selecting a Month gives wierd results. Weeks still works fine. Have another Idea?