cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors