DAX on Quarter to Quarter comparison

[CLANG10]

Hi,

   Would like to seek advise on how to write DAX measure to compare Current quarter average % change Vs previous quarter % change.

I tried to write as:

Current quarter = CALCULATE([% change], FILTER(Max[QUARTER])  

Previous quarter = CALCULATE([% change], FILTER(Max[QUARTER]-1)  

Difference = Current quarter - Previous quarter

But it doesn't work, as writing this way, it seems to intepret that if current quarter = 202301, then previous quarter= 202300 instead of 202212

Thanks for advise!

QUARTER	MONTH	ACTUAL_DATE	% change
202203	202208	8/26/2022 0:00	100%
202203	202209	8/29/2022 0:00	100%
202203	202209	8/30/2022 0:00	50%
202203	202209	8/31/2022 0:00	100%
202203	202209	9/1/2022 0:00	100%
202203	202209	9/2/2022 0:00	100%
202203	202209	9/5/2022 0:00	100%
202203	202209	9/6/2022 0:00	100%
202203	202209	9/7/2022 0:00	0%
202204	202211	11/25/2022 0:00	50%
202204	202212	11/28/2022 0:00	100%
202204	202212	12/17/2022 0:00	100%
202204	202212	12/20/2022 0:00	100%
202204	202212	12/21/2022 0:00	75%
202204	202212	12/22/2022 0:00	100%
202204	202212	12/27/2022 0:00	50%
202204	202212	12/28/2022 0:00	100%
202301	202301	1/3/2023 0:00	100%
202301	202301	1/4/2023 0:00	100%
202301	202301	1/6/2023 0:00	67%
202301	202302	2/24/2023 0:00	100%
202301	202303	3/17/2023 0:00	100%
202301	202303	3/20/2023 0:00	50%
202301	202303	3/21/2023 0:00	100%
202301	202303	3/22/2023 0:00	100%
202301	202303	3/23/2023 0:00	0%
202301	202303	3/24/2023 0:00	67%
202301	202303	3/28/2023 0:00	100%
202301	202303	3/29/2023 0:00	100%
202301	202303	3/30/2023 0:00	100%
202301	202303	3/31/2023 0:00	100%
202302	202304	4/2/2023 0:00	100%

[v-tangjie-msft · Accepted solution]

Hi  @CLANG10 ,

Here are the steps you can follow：

1.Create a calculated column.

QUARTER_Number = QUARTER('Table'[ACTUAL_DATE])

2.Create  measures.

Current_Avg =

AVERAGEX(

   FILTER(ALL('Table'),

   'Table'[QUARTER]=MAX('Table'[QUARTER])),[% change])

Previous_Sum =

var _date=

DATE(

   YEAR(MAX('Table'[ACTUAL_DATE]))-1,12,1)

var _lastdate=

VALUE(YEAR(_date)&"0"&QUARTER(_date))

return

IF(

   MAX('Table'[QUARTER_Number])=1,

  SUMX(

      FILTER(ALL('Table'),

      'Table'[QUARTER]=_lastdate),[% change]),

      SUMX(

          FILTER(ALL('Table'),

          'Table'[QUARTER]=MAX('Table'[QUARTER])-1),[% change]))

3.Then the result is as follows.

Best Regards,

Neeko Tang

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