Difference between two rows

[mohany2211]

Hi..........i am trying to calculate the revenue growth between the two dates and based on criteria from another column, but not able to apply the correct formula. can someone help me with a formula to calcuate the same. Here is a small table with the data and expected result

thanks in advance for your help 

[Anonymous]

Hi @mohany2211,

I have taken your sample data and created a measure for your requirement. its working fine but could you please confirm is this your requirement.

Note: Please find the PBIX file i have attached here with name Remarks.pbix

Best Regards,

Ravi

[mohany2211]

Dear Ravi...thanks for the same. However...my issue is if previous figure is negative and current figure is poistive than also growth is coming as -ve refer table in my previous post.

Can you pls help how to rectify that

[Anonymous · Accepted solution]

Hi @mohany2211,

In Previous Value you can use ABS(PreviousVaue). After return statement in my dax you can change this.

"
Return
DIVIDE((CurrentValue-PreviousValue),ABS(PreviousValue)) "

I am also attaching Pbix file with update. Please follow the link Remarks.pbix

Best Regards, 

Ravi

[dobregon]

Hi,

I think you need to do 2 things.

1. Create a column with previous value

• You need to create a column that has the previous value (previous month as i see in your excel), try something like
  

  previous value = calculate(value or max (table[revenue]), filter(table, table[date]=date(year(date),month(date)-1,day(date))

  *Try to do this or add another filter

2. Create the measure revene growth like (revenue - prev revenue) / prev revenue

[Zubair_Muhammad]

@mohany2211

If you need a  calculated column then

Calc Column =
VAR previousvalue =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Remarks] = EARLIER ( [Remarks] )
                    && [Date] < EARLIER ( [Date] )
            ),
            [Date], DESC
        ),
        [Revenue]
    )
RETURN
    IF ( NOT ( ISBLANK ( previousvalue ) ), [Revenue] / previousvalue - 1 )

[mohany2211]

Hi Zubair... the formula is working. However, there is one scenario which is giving incorrect result where growth is moving from -ve to +ve between two month than the value is coming as negative. Refer below 

Date	Entity	Revenue	Column
June 2018	BLUE	(10,961)
July 2018	BLUE	(28,239)	157.62%
August 2018	BLUE	107,039	-479.05%
June 2018	ABKID	(11,520)
July 2018	ABKID	(28,797)	149.97%
August 2018	ABKID	106,480	-469.75%