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

Question

I want to display percentage increase or decrease in total for each month as I select each month i.e when I click on FEB, it should tell me whether there was a percentage increase/decrease in expenses compared to JAN.

I have tried different codes but keep getting an error message.

Here is a DAX CODE I tried:

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

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

I want a new column created to display the percentage increase or decrease from a month to its previous month. Here is a screenshot of the excel document:

The problem is your definition of "last month". You should use dates instead of month name. Make this change, and then post a data sample or a pbix file. — RADO

Aldert Aldert · Accepted Answer · 2019-07-13T08:06:39

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 ( population[TOTAL] ),
        FILTER (
            population,
            DATEDIFF(population[MONTH], EARLIER ( population[MONTH] ),MONTH) = 1
                && population[CATEGORY] = EARLIER ( population[CATEGORY] )

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

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

1 Answers