Creating a displayed month - previous month variance column in pivot table.

HClegg — Mon, 12 Jun 2023 09:00:07 GMT

I've created a pivot table in one of my apps. I have a list of dimensions that represent various customer details such as company name. I then have measures set up where I can track sales performance, e.g. sales turnover, profit etc.

I also have comparison buttons that add new dimensions to the pivot table once clicked, one of these being the variance.

I'm trying to change the variance to a month on prev month, whereas currently it is year on previous year. So, for February, instead of the variance showing February 2023 against February 2022, I want it to show February 2023 variance against January 2023.

Below is the previous code to calculate the sum of customer turnover:
sum({$<Year=, Quarter=, Month=, Week=, Day= , Date = {"$(=vRange_TY)"}>}[Sales Amount Turnover])
vRange_TY defines that the date range should look at this year (previous year variable = vRange_LY, I've tweaked this variable to =MonthName([Date])

New code to calc sum of customer turnover:
sum({$<Year=, Quarter=, Month=, Week=, Day= , Date = {"=MonthName([Date])"}>}[Sales Amount Turnover])

Now, to calculate the variance measure, I created the below formula:
Above(sum({$<Year=, Quarter=, Month=, Week=, Day= , Date = {"=MonthName([Date])"}>}[Sales Amount Turnover]))
-
sum({$<Year=, Quarter=, Month=, Week=, Day= , Date = {"=MonthName([Date])"}>} [Sales Amount Turnover])

However, the data isn't correct, I've attached a screen shot of the returned data. As you can see on row 5, March Sales £ CP (sales turnover) is £848, and February is £1414, the variance on this should be -£566. However, the Sales £ Var (variance) shown under March is -£673.16

Not sure what I've done wrong here. This is my first community post so I apologise if I've over/under explained or been vague on any parts of this. If you could let me know any additional details I'll happily provide.

topic Creating a displayed month - previous month variance column in pivot table. in App Development

Creating a displayed month - previous month variance column in pivot table.