topic Calculation of growth percentage between months / Quarters / Years in matrix in DAX Commands and Tips

Calculation of growth percentage between months / Quarters / Years in matrix

urielp — Wed, 14 Dec 2022 17:52:12 GMT

Hey ,

I have a matrix like this:

Category	January	February	March	April	May	June
a	100	1250	1350	1550	1650	1750
b	200	2250	2350	2550	2650	2750

To display the correct balance also in the display by quarters like this:

Category	Q1	Q2	Q3	Q4
a	1350	1750
b	2350	2750

i used this code:

TotalAssetEOPeriod =

CALCULATE(

MyFact[TotalAsset],

LASTNONBLANK(

PARALLELPERIOD(

'Dim_Calendar'[Date],

MONTH),

MyFact[TotalAsset]))

*** If I change from 0 to -1 the results are incorrect

How do I show the balance for the previous period?

So that I can calculate a periodic growth rate

Thanks in advance

Re: Calculation of growth percentage between months / Quarters / Years in matrix

v-xiaosun-msft — Thu, 15 Dec 2022 09:24:35 GMT

Hi urielp ,

According to your description, I made a sample and here is my solution.

Sample data:

We need to use the "Merged" which is a date type. Create a column to return the Quarter.

Quarter = VAR _a = QUARTER ( 'Sheet1'[Merged] ) RETURN SWITCH ( _a, 1, "Q1", 2, "Q2", 3, "Q3", 4, "Q4" )

Then create a column to return balance.

Column = IF ( CALCULATE ( MAX ( 'Sheet1'[Merged] ), FILTER ( 'Sheet1', 'Sheet1'[Quarter] = EARLIER ( Sheet1[Quarter] ) ) ) = 'Sheet1'[Merged], 'Sheet1'[Balance] )

Put them into a matrix as below, you will get the expected output.

I attach my sample below for your reference. If this does not help, please provide us your sample(PBIX. file) in order that we can help you further more.

Best Regards,
Community Support Team _ xiaosun

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

Re: Calculation of growth percentage between months / Quarters / Years in matrix

urielp — Thu, 15 Dec 2022 12:19:47 GMT

Hi v-xiaosun-msft,

Thank you for the answer

the example data in the table you gave is correct
but I did not understand

My problem is to find the growth percentage like here:

Category	Q1	Q2	Q3
	Balance	GrowthPercent	Balance	GrowthPercent	Balance	GrowthPercent
a	1350	0%	1750	29.6%	1800	2.9%
b	2350	0%	2750	17.0%	2800	1.8%

Thanks in advance

Re: Calculation of growth percentage between months / Quarters / Years in matrix

v-xiaosun-msft — Wed, 28 Dec 2022 07:36:23 GMT

Hi urielp ,

Sorry for late coming.

Follow these steps at the basis of the solution above.

Create a calculated column.

rank = RANKX ( FILTER ( 'Sheet1', 'Sheet1'[Category] = EARLIER ( Sheet1[Category] ) && 'Sheet1'[Column] <> BLANK () ), 'Sheet1'[Column], , ASC )

Then create a calculated column to return the growth percentage.

growth percentage = VAR _a = CALCULATE ( MAX ( 'Sheet1'[Column] ), FILTER ( 'Sheet1', 'Sheet1'[Category] = EARLIER ( Sheet1[Category] ) && 'Sheet1'[rank] < EARLIER ( Sheet1[rank] ) ) ) RETURN IF ( 'Sheet1'[rank] > 1, ( 'Sheet1'[Column] - _a ) / _a, 0 )

Put "growth percentage" in the matrix, you will get the expected output.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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