Compare the values with years that has gap

[pathare_amey]

In the below image, i have 3 columns, Country from 1 table, Year from Calendar table and [Total Ratings] is a measure. I wanted to calculate the previous year ratings for each country and there are gap between years. There are random years for each country and have gap period. I want to Calculate the Total Ratings for each country based on previous year if there is no previous year then compare with the above row. For eg. Take Country Argentina, I want to compare Total Ratings of 2020 with 2019 but 2019 is not present so it should compare it with year 2017.

[danextian · Accepted solution]

Hi @pathare_amey 

Try the measure below

Previous Year = 
VAR PrevYear =
    //get the year before current
    CALCULATE (
        MAX ( 'table'[year] ),
        FILTER (
            ALLEXCEPT ( 'table', 'table'[country] ),
            'table'[year] < MAX ( 'table'[year] )
        )
    )
VAR PrevYearValue =
    //get the value equivalent to PrevYear
    CALCULATE (
        [Total Ratings],
        FILTER ( ALLEXCEPT ( 'table', 'table'[country] ), 'table'[year] = PrevYear )
    )
RETURN
    //difference
    IF ( NOT ( ISBLANK ( PrevYear ) ), [Total Ratings] - PrevYearValue )

You should get a similar result as below:

Moving forward, please provide a sample data that we can easily copy-paste.

[danextian · Accepted solution]

Hi @pathare_amey 

Try the measure below

Previous Year = 
VAR PrevYear =
    //get the year before current
    CALCULATE (
        MAX ( 'table'[year] ),
        FILTER (
            ALLEXCEPT ( 'table', 'table'[country] ),
            'table'[year] < MAX ( 'table'[year] )
        )
    )
VAR PrevYearValue =
    //get the value equivalent to PrevYear
    CALCULATE (
        [Total Ratings],
        FILTER ( ALLEXCEPT ( 'table', 'table'[country] ), 'table'[year] = PrevYear )
    )
RETURN
    //difference
    IF ( NOT ( ISBLANK ( PrevYear ) ), [Total Ratings] - PrevYearValue )

You should get a similar result as below:

Moving forward, please provide a sample data that we can easily copy-paste.

[pathare_amey]

Thank you!! @danextian  The Solution is giving results, if possible can you explain this? how logic has been implemented?

[danextian · Accepted solution]

VAR in DAX means variable. Instead of writing a formula everytime it is needed, it is assigned to a variable. The concept is similar  to other programming languages. This makes the code shorter and easier to understand.

The variable  below calculates the year before current for each country using ALLEXCEPT. It removes context filters over a table except for columns that are specified in subsequent arguments. In other words,  the aggregation (which is MAX in this case) is calcualted at each country (column specified) in the table.

VAR PrevYear =
    //get the year before current
    CALCULATE (
        MAX ( 'table'[year] ),
        FILTER (
            ALLEXCEPT ( 'table', 'table'[country] ),
            'table'[year] < MAX ( 'table'[year] )
        )
    )

The variable below calculates the Total Ratings for each country in the table which year is equivalent to variable PrevYear.

VAR PrevYearValue =
    //get the value equivalent to PrevYear
    CALCULATE (
        [Total Ratings],
        FILTER ( ALLEXCEPT ( 'table', 'table'[country] ), 'table'[year] = PrevYear )
    )

RETURN is used for the result you want which can be any of the variables above or another calculation or an external measure.  It is used only if VAR is used. The below result could have been assigned to VAR Result and instead of wrign down the formula below Result is returned instead.

The IF condition is added so the result is shown only if the previous year is not blank otherwise it will have returned the current [Total Ratings] as there is nothing to subract from it.

RETURN
    //difference
    IF ( NOT ( ISBLANK ( PrevYear ) ), [Total Ratings] - PrevYearValue )