Same period last year but categorical period

[Anonymous]

Hello everyone. I would like to calculate the growth between time periods but written categorically. 

I want to compare P13 from 2021 to P13 from 2020. Since I do not have exact dates SAMEPERIODLASTYEAR is not accurate.

Any thoughts on how to do that?

[amitchandak]

@Anonymous , Make sure you have separate date or period table with numeric Year and period , then try measure like

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))

The way we handle week

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

i think you understood that I just want to compare those 2 values. What i meant was an example. I want to have a comparison between all the values not just P13 2020 2021

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First go to query editor>duplicate column Period>Split the new column into 2 rows as below:

Rename the column names as "Year"and "_Period";

Then create a measure as  below:

difference =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Year] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Store] = MAX ( 'Table'[Store] )
                && 'Table'[_Period] = MAX ( 'Table'[_Period] )
        )
    )
VAR _smallyear =
    CALCULATE (
        MIN ( 'Table'[Year] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Store] = MAX ( 'Table'[Store] )
                && 'Table'[_Period] = MAX ( 'Table'[_Period] )
        )
    )
VAR _bigyear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Store] = MAX ( 'Table'[Store] )
                && 'Table'[_Period] = MAX ( 'Table'[_Period] )
        )
    )
VAR _sales1 =
    CALCULATE (
        SUM ( 'Table'[sales_value_eur] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Store] = MAX ( 'Table'[Store] )
                && 'Table'[_Period] = MAX ( 'Table'[_Period] )
                && 'Table'[Year] = _smallyear
        )
    ) + 0
VAR _sales2 =
    CALCULATE (
        SUM ( 'Table'[sales_value_eur] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Store] = MAX ( 'Table'[Store] )
                && 'Table'[_Period] = MAX ( 'Table'[_Period] )
                && 'Table'[Year] = _bigyear
        )
    ) + 0
RETURN
    IF (
        _count < 2
            || MAX ( 'Table'[Year] ) = _smallyear,
        _sales1,
        _sales2 - _sales1
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

Doesnt work actually. I believe due to max usage. However, if you remove max, it doesnt work also.