dax: custom time intelligence

[hood2media]

hi,

as an intro, i am doing analysis on flight performance data (specifically on the durations of actual flying, taxi-in, taxi-out, delays etc) for the past few years. in the airline industry, there are northern summer and northern winter seasons where:

a. the northern summer
   begins from the last sunday in march and ends with the last saturday in october each year

b. the northern winter  

   begins from the last sunday in october and ends with the last saturday in march (the following year) each year

flight performance analysis is done between summer periods or winter periods such as the followings

example 1 - summer 2019 vs summer 2020
(summer 2019 is between sunday 31-mar-2019 - saturday 26-october-2019 &
summer 2020 is between sunday 29-mar-2020 - saturday 24-october-2020)

or

example 2 - winter 2019 vs winter 2020.
(winter 2019 is between sunday 27-october-2019 - saturday 28-mar-2020 &
winter 2020 is between sunday 25-october-2020 - saturday 27-mar-2021)

i'd like to create a custom time intelligence using calculation group to analyze flight performance between the corresponding summer or winter seasons (i.e. non-contigous periods). can i get assistance on that pls?

i have attached a sample pbix file for this here. in the master_data table, i hv already created a column to indicate the different seasons in there.

tks & krgds,
-nik

[lbendlin]

These columns need to go into your Calendar dimension table, not in the fact table.

[hood2media]

tks @lbendlin.

i hv corrected the calendar table (& also uploaded it to the same location in the weblink mentioned above).

i stil hope to find a way to getting the formula for a custom calculation group on the seasons for this case?

tks & kgrds,

-nik

[lbendlin · Accepted solution]

I simplified your calendar table a bit more and also introduced a season number.  Both for sorting the season title and for doing the time intelligence stuff.

See attached for the example of using custom "SAMEPERIODLASTYEAR"  calculations. As you can see the total value is missing.  If you want that then you will have to totally rewrite the AVERAGE function (which kinda negates the calculation group)

[hood2media]

many thanks again, @lbendlin.

seems ok wth me but let me take a look again & cfm if all is good to go.

krgds,

-nik