Breaking up year over year changes into component parts

DAX DAX Calculations
1

Hi,

Not sure if this is the best section for it but here it goes.

Traditionally we have broken up our year over year changes into two parts, a volume and a price variance. Those variances then add up to the total variance. The current forumula’s I have for them are

volume Variance =(  ( [volume this year] - [volume last year] ) * [price last year] ) / [revenue last year]
price variance  = ( ( [price this year] - [price last year] ) *[volume this year] ) / [revenue last year]

But what I realized is that this gives a false picture because the number of selling days (week days) differs.

So what I think I need is something like

Volume variance = [volume per sales day this year] – [volume per sales day last year] )  * [price last year] ) / [revenue last year]
price variance  = ( ( [price this year] - [price last year] ) *[volume this year] ) / [revenue last year]

but the numbers don’t look right. I think I probably need a third piece something like

variance due to number of days = [average volume per day this year ] / [volume last year]

or something like that but I’m not quite sure. Before everything added up real nice.

Thoughts?

2

Hi Mathew,

Can you add images or a file of what you are looking at?

It’s just too difficult to look at formulas and know everything that is at play within the scenario.

The more info the better if you can on this one.

My feel is you’ll need to use iterating function to calculate this correctly, but will have to review more.

For more information on these types of functions and what they do, check here.

If you have a file to look at on this, they will solve it the quickest.

Chrs