Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
urielp
Frequent Visitor

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

Hey ,
 
I have a matrix like this:
 
CategoryJanuary February March April   May  June
a10012501350155016501750
b20022502350255026502750

 

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

 

CategoryQ1 Q2 Q3 Q4 
a13501750  
b23502750  

 

 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
 
Thanks in advance
3 REPLIES 3
urielp
Frequent Visitor

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:

 

CategoryQ1 Q2 Q3
 BalanceGrowthPercentBalanceGrowthPercentBalanceGrowthPercent
a13500%175029.6%18002.9%
b23500%275017.0%28001.8%

 

Thanks in advance

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.

vxiaosunmsft_0-1672212828323.png

 

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.

v-xiaosun-msft
Community Support
Community Support

Hi @urielp ,

 

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

Sample data:

vxiaosunmsft_0-1671096075770.png

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.

vxiaosunmsft_1-1671096177078.png

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors