cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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

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],

0,

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

3 REPLIES 3
Frequent Visitor

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%

Community Support

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.

Community Support

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors