calculate percentage different of total amount between two years

[Anonymous]

Hi, sorry im new to power BI. i tried to calculate the percentage different of totalamount between two years but failed.

steps that i had performed.

1) edit queries, i have 3 columns( monthname,totalamount,year), i pivot monthname with totalamount.

2) in the table result when i select the these three columns it will show as below

3) how can i insert the formula to calculate the percentange different of totalamount between this two years and show the result in new row ?

expected output. 

year   | september    |  october

2018     RM267068     RM677855

2019     RM1205141   RM968686

Diff.                3.512            0.42

Total     RM1,205141   RM968.686

[Ashish_Mathur]

Hi,

What result would you expect when you have more than 2 years of data?

[v-yuta-msft · Accepted solution]

@Anonymous ,

In query editor, click september and october, then click "unpivot", after apply&close like below:

Then create a calculate column using dax below:

diff = 
VAR Current_Year = 'Table'[year]
VAR Previous_Year = Current_Year - 1
RETURN
    SWITCH (
        'Table'[Attribute],
        "september", (
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = "september"
                        && 'Table'[year] = Current_Year
                )
            )
                - CALCULATE (
                    MAX ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Attribute] = "september"
                            && 'Table'[year] = Previous_Year
                    )
                )
        )
            / CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = "september"
                        && 'Table'[year] = Previous_Year
                )
            ),
        "october", (
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = "october"
                        && 'Table'[year] = Current_Year
                )
            )
                - CALCULATE (
                    MAX ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Attribute] = "october"
                            && 'Table'[year] = Previous_Year
                    )
                )
        )
            / CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = "october"
                        && 'Table'[year] = Previous_Year
                )
            )
    )

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.