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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BobKoenen
Helper IV
Helper IV

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

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

 

DateNormal weekISO week numerMonth
1-1-20211531
2-1-20211531
3-1-20201531
4-1-2020111
5-1-2020111

 

In 2020 it looks like:

 

datenormal week numberIso week numermonth
1-1-2020111
2-1-2020111
3-1-2020111
4-1-2020111

 

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. 



1 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:

v-kelly-msft_0-1611279526864.png

v-kelly-msft_1-1611279559888.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

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

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

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?

v-kelly-msft_0-1611039293922.png

Best Regards,
Kelly

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

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

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:

v-kelly-msft_0-1611108759573.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

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

@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:   

BobKoenen_1-1611244501867.png

 

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:

v-kelly-msft_0-1611279526864.png

v-kelly-msft_1-1611279559888.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

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

amitchandak
Super User
Super User

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

@amitchandak 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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