POWER PIVOT : Calculate Growth Over PREVIOUS ROW / Previous Date . DAX-CodePudding

Table Picture

Pivot Table Picture

GOAL :

POS|MONTH|VALUE|GROWTH
AG|april| 20  | *null*
AG|may  | 40  | 100%
AG|june | 10  | -75%
AG|july | 15  |  50%
TB|april| 20  | *null*
TB|may  | 40  | 100%
TB|june | 10  | -75%
TB|july | 15  |  50%

It's in Spanish but you get the gist of it. The rows represent each month and I want to create a Column for each value (those as columns) with the growth over the last month.

Thank you so much in advance !!

Ignacio

PD : I found lots of solutions out there, Both in English and in Spanish and tried each and one of them without success. I used different formulas for measures and calculated columnas but , for example, there's at least 3 scenarios that I stumbled upon : Either ∞ as returned values ; None values using EARLIER ; SELECTEDVALUE not being on excel yet ; LOOKUPVALUE returning that multiple values were supplied but one was expected ... I actually tried but I couldn't and I'm with a bit of a hurry :(

example of a formula that makes sense but Null values are returned:

=CALCULATE(SUM('TABLE'[ATENCIONES]);FILTER('TABLE';
'TABLE'[MONTH]=PREVIOUSMONTH(STARTOFMONTH('TABLE'[MONTH]))                              
))

UPDATE :

This formula below works but only for that filter that I wrote.

= VAR PrevMonth
        = PREVIOUSMONTH(STARTOFMONTH('5 VALORES'[MES]))
RETURN  
        DIVIDE (
            CALCULATE ( SUM ( '5 VALORES'[ATENCIONES] ) ;'5 VALORES'[POS]="ANGEL GALLARDO");
            CALCULATE (
                SUM ( '5 VALORES'[ATENCIONES] );
                FILTER (
                    ALL( '5 VALORES');
                    '5 VALORES'[MES] = PrevMonth
                )
            ;'5 VALORES'[POS]="AG")
        )

CodePudding user response：

You can use the PREVIOUSMONTH function to get the value you need to calculate growth. Something like this:

Growth:=var curmonth = SUM('TABLE'[ATENCIONES])
var prevMonth = CALCULATE(SUM('TABLE'[ATENCIONES]), PREVIOUSMONTH('Calendar'[Date]))  

RETURN

IF(curmonth=0,BLANK(),DIVIDE(curmonth - prevMonth,prevMonth))

The catch with using PREVIOUSMONTH is that you need to using a date table and create the proper relationship. Creating a date table is as easy as opening the Data Model > Design tab > Date Table > New

CodePudding user response：

Thanks for the help! I found an answer, not that neat of a code, but at least works ...

- ORIGINAL TABLE :

POS|MONTH|VALUE|GROWTH

AG|april| 20  | *null*

AG|may  | 40  | 100%

AG|june | 10  | -75%

AG|july | 15  |  50%

TB|april| 20  | *null*

TB|may  | 40  | 100%

TB|june | 10  | -75%

TB|july | 15  |  50%

- DAX CODE FOR POWER PIVOT:

GROWTH:=

  VAR PrevMonth
        = PREVIOUSMONTH(STARTOFMONTH('TABLE'[MONTH]))
  VAR currPOS
        = 'TABLE'[POS]
  VAR PrevValue
        =   CALCULATE (
                SUM ( 'TABLE'[Value] );
                FILTER (
                    ALL( '5 VALORES');
                    'TABLE'[MONTH] = PrevMonth
                )
            ;'TABLE'[POS]=currPOS)
    RETURN  
        IF( ISBLANK(PrevValue);BLANK();
            DIVIDE (
                'TABLE'[Value];
                PrevValue
            )-1)