Forum Discussion

hood2media's avatar
hood2media
Resolver II
2 years ago
Solved

dax: custom time intelligence

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's avatar
    lbendlin
    2 years ago

    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)

     

     

4 Replies

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

    • hood2media's avatar
      hood2media
      Resolver II

      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's avatar
        lbendlin
        Super User

        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)