Summarize differences between every 5 years?

[Anonymous]

Hi,

I'd like to know if there's a way to summarize differences between every 5 years in DAX? Basically, I got a measure to calculate difference of values between 2022 and 2017, 2017 and 2012, and so on (using PARALLELPERIOD). Now, I kind of need to summarize those differences.

Hope someone can help. Thanks!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Table'[Date])

Flag =
var _today=TODAY()
var _yeartoday=YEAR(_today)
var _round=
ROUNDUP(
('Table'[Year] - _yeartoday ) /5,0)
return
IF(
    'Table'[Year]=_yeartoday,-1,_round)

Title =
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Flag]=EARLIER('Table'[Flag])),[Year])
var _minx=MINX(FILTER(ALL('Table'),'Table'[Flag]=EARLIER('Table'[Flag])+1),[Year])
var _indexmax=MAXX(ALL('Table'),[Flag])
return
IF(
    'Table'[Flag]=_indexmax,
   YEAR(TODAY()) &" and "& _minyear,
 _minx &" and "& _minyear
)

Sum all =
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Flag]=EARLIER('Table'[Flag])),[Year])
var _minx=MINX(FILTER(ALL('Table'),'Table'[Flag]=EARLIER('Table'[Flag])+1),[Year])
var _indexmax=MAXX(ALL('Table'),[Flag])
return
IF(
    'Table'[Flag]=_indexmax,
    SUMX(FILTER(ALL('Table'),[Year]>=_minyear &&[Year]<=YEAR(TODAY())),[Value]),
    SUMX(FILTER(ALL('Table'),[Year]>=_minyear &&[Year]<=_minx),[Value])
)

2. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[Title],'Table'[Sum all])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly