Percentage change from category to category

Question

I will like to create a new column that calculates the percentage change from JAN-FEB-MAR-APR-MAY-JUN which are in the COLUMN "MONTH" based on the COLUMN "TOTAL". Here is a script I have tried but its not working

change perc = 
VAR ValueLastMONTH =
    CALCULATE (
        SUM ( Sheet1[TOTAL] ),
        FILTER (
            Sheet1,
            Sheet1[MONTH]
                = ( EARLIER ( Sheet1[MONTH] ) - 1 )
                &&Sheet1 = EARLIER ( Sheet1[CATEGORY] )

        )
    )
RETURN
    IF (
        ISBLANK ( ValueLastMONTH ),
        0,
        ( Sheet1[TOTAL] - ValueLastMONTH )
            / ValueLastMONTH

Here is the link to the power bi file. enter link description here

Possible duplicate of I will like a script in power bi that will calculate percentage increase or decrease from one month to the previous month — Aldert

Aldert Aldert · Accepted Answer · 2019-07-12T16:32:23

The Column 'Month' is not of type date. How would PowerBi know the text APR represents April? You need to make this column a date.

Now you need to change the script to work with DateDiff:

change perc = 
VAR ValueLastMONTH =
    CALCULATE (
        SUM ( Sheet1[TOTAL] ),
        FILTER (
            Sheet1,
            DATEDIFF(Sheet1[MONTH], EARLIER ( Sheet1[MONTH] ),MONTH) = 1
                && Sheet1[CATEGORY] = EARLIER ( Sheet1[CATEGORY] )

        )
    )
RETURN
    IF (
        ISBLANK ( ValueLastMONTH );
        0; 
        ( Sheet1[TOTAL] - ValueLastMONTH )
            / ValueLastMONTH)

Percentage change from category to category

1 Answers