Creating a measure to calculate change between seasons

[vic_aguirreV]

I have the following table:

The high season category, comes from the Calendar table, that works like this:

Season =

VAR curryear = YEAR('Calendar'[ShortDate])

VAR nexty = YEAR('Calendar'[ShortDate]) + 1

VAR lasty = YEAR('Calendar'[ShortDate]) - 1

RETURN

IF(

    MONTH('Calendar'[ShortDate]) in {1, 2, 3, 9, 10, 11, 12},

    IF(

        MONTH('Calendar'[ShortDate]) in {1, 2, 3}, --como la temporada va de setiembre a marzo, se toma el año anterior para ene-mar, pues son los meses finales de una temporada alta

        "High Season " & lasty & "-" & curryear,

        "High Season " & curryear & "-" & nexty

    ),

    "Low Season" &"-"& curryear

)

So in essence, it is a column that checks to what season a month in a given year, belongs to. Now I would like to calculate the change from season to season. Any tips on how to go about this?

[vic_aguirreV]

Thanks @ryan_mayu! That actually did help, and here is what I did. The calendar table has all the dates from transactions, and it has the following categorization for season:

Season =

VAR curryear = YEAR('Calendar'[ShortDate])

VAR nexty = YEAR('Calendar'[ShortDate]) + 1

VAR lasty = YEAR('Calendar'[ShortDate]) - 1

RETURN

IF(

    MONTH('Calendar'[ShortDate]) in {1, 2, 3, 9, 10, 11, 12},

    IF(

        MONTH('Calendar'[ShortDate]) in {1, 2, 3}, --como la temporada va de setiembre a marzo, se toma el año anterior para ene-mar, pues son los meses finales de una temporada alta

        "High Season " & lasty & "-" & curryear,

        "High Season " & curryear & "-" & nexty

    ),

    "Low Season" &"-"& curryear

)

(apologies for the spanish comments, this is for the team lol)

Then it categorizes in an additional column, what type of season it is:

Season Type = IF(MONTH('Calendar'[ShortDate]) in {1,2,3,9,10,11,12},"High Season","Low Season")

Finally I rakend the seasons, so we can index them so that we can get some filters for our eventual calculation:

Season Index =

RANKX(

    ALL('Calendar'),

    'Calendar'[Season],

    ,

    ASC,

    DENSE

)

Once all this was done, I then proceeded to calculate the app rate change:

App Rate Change =

VAR CurrentIndex = SELECTEDVALUE('Calendar'[Season Index]) --en la tabla calendar hay un indice por cada temporada

VAR PreviousIndex = CurrentIndex - 1 -- esto nos devuelve el indice de la temporada pasada para poder calcular su app rate

VAR PreviousAppRate = CALCULATE(

    [App Rate],

    FILTER(

        ALL('All CC Trx'),  -- Se usa ALL para eliminar cualquier otro filtro que pueda estar afectando los resultados

        'All CC Trx'[Season Index] = PreviousIndex

    )

)

VAR CurrentAppRate = [App Rate]

RETURN

    IF(

        PreviousIndex = 0 || ISBLANK(PreviousAppRate) || ISBLANK(CurrentAppRate),

        BLANK(),  -- Retorna BLANK si no hay temporada anterior o si alguno de los App Rates es BLANK

        (CurrentAppRate - PreviousAppRate) / PreviousAppRate  -- Calcula el cambio porcentual

    )

the idea here was to get the index of the season in the row with selected value, then get the previous season's index,  then calculate the previous season app rate, based on the index. Once I have this, I can now calculate the current's season app rate, so that we can calculate the change. 

the return then gives us the change vs the previous season.

Thanks for the help (:

[ryan_mayu]

@vic_aguirreV 

pls provide some sample data and expected output. I didn't see low season in your screenshot. Do you want to compare all high seasons or low seasons, or between high season and low season?

[vic_aguirreV]

Yes, my apologies for the lack of info.

I have the visualization filtered only for high seasons. The idea here is to take for example HH 2021-2022, then compare it to HH 2022-2023. The first one has an app rate of 78.79%, and the second one has an app rate of 75.97%. In this case, the change vs the previous season would be (75.97%-78.79%) / 78.79%, for a total change of -3.57%. The same would apply for the 2nd one vs the 3rd one, and so on. Naturally the first one would show a blank, since there is no previous season. Does that make it a bit clearer?

[ryan_mayu · Accepted solution]

Since you didnot provide the sample data. I am not sure if the soluion works for you.

For me, I will create two columns , Season type, High or Low and year column 

2021-2022 to be 2021; 2022-2023 to be 2022

=maxx(filter(all(table), seasontype=max(seasontype) and year =max(year)-1),apprate)

Then you can get the rate for last row.