Calculate the variance of the rows above

[PBI_LUCKY]

Hi team,

Is there a way in PBI that we can calculate the difference of the rows above? Refer to the table below:

• I would like to calculate the different between sale in 2024 and 2022 for each month. The year can be change (e.g 2021 vs 2023, so it is not comparing current year to prior year). Result is the last 2 rows.

Thanks

Year	Type	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL
2024	Sale	50	50	50	50	50	50	50	50	50	50	50	50	600
	Cost	20	20	20	20	20	20	20	20	20	20	20	20	240
2022	Sale	30	30	30	30	30	30	30	30	30	30	30	30	360
	Cost	10	10	10	10	10	10	10	10	10	10	10	10	120
Var	Sale	20	20	20	20	20	20	20	20	20	20	20	20	240
	Cost	10	10	10	10	10	10	10	10	10	10	10	10	120

[Anonymous]

Hi @PBI_LUCKY ,

Try the following expression:

PreviousYearSameMonthDifference = 
VAR CurrentMonth = MONTH(MAX('Table'[Date]))
VAR CurrentYear = YEAR(MAX('Table'[Date]))
VAR PreviousYear = CurrentYear - 2
RETURN
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        YEAR('Table'[Date]) = PreviousYear &&
        MONTH('Table'[Date]) = CurrentMonth
    )
) - SUM('Table'[Sales])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[PBI_LUCKY]

Thanks but it is no the way I want it. I want the calculation to be dynamic. It means I can compare not only 2022 vs 2024 but also other e.g 2020 vs 2024.

[Anonymous]

Hi @PBI_LUCKY ,

You can create a parameter that changes PreviousYear to the following expression:

VAR PreviousYear = CurrentYear - [parameter]

Use parameters to visualize variables - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[lbendlin]

Yes, this can be done with Visual Calculations which actually has a concept of "the row above".  Give that a try.

[PBI_LUCKY]

Thanks but it doesn't work since it is only allowed adding the column but not the row. I think the dataset was set up that all value is in the same column (e.g in the dataset sales & exp amount are on the same column and there is an extra column next to it definte whether it is sales or exp). Cheers