Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

Measure for Previous year with custom date table.pbix114 KB

v-kelly-msft

Community Support

5 years ago

Hi Anonymous ,

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!

Anonymous

Not applicable

5 years ago

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_cBKw7pZEQQEg2APFtK832PXQ?e=VmlCIf

v-kelly-msft

Community Support

5 years ago

Hi Anonymous ,

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!

Measure for Previous year with custom date table.pbix114 KB

Anonymous

Not applicable

5 years ago

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

Community Support

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

Measure for Previous year with custom date table.pbix114 KB

Forum Discussion

Measure for Previous year with custom date table

Related content

Previous Month Measures

Previous Year Measure for Line Chart

Previous YTD measure

Previous year measure

Previous Period measure