topic Calculate the variance of the rows above in DAX Commands and Tips

Calculate the variance of the rows above

PBI_LUCKY — Tue, 20 Aug 2024 09:27:04 GMT

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

Re: Calculate the variance of the rows above

lbendlin — Wed, 21 Aug 2024 00:40:46 GMT

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

Re: Calculate the variance of the rows above

Anonymous — Wed, 21 Aug 2024 02:53:09 GMT

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.

Re: Calculate the variance of the rows above

PBI_LUCKY — Wed, 21 Aug 2024 20:54:35 GMT

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

Re: Calculate the variance of the rows above

PBI_LUCKY — Wed, 21 Aug 2024 21:01:20 GMT

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.

Re: Calculate the variance of the rows above

Anonymous — Thu, 22 Aug 2024 01:07:45 GMT

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.