cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Calculating Variance: Multiple Years on a table

Hi, 

 

I am wanting to show the variance in this table between the different years (Compare 2016 to 2015 and 2015 to 2014. 

 

var.png

 

I am unsure how to achieve this result. 

 

Any advice?

 

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Memorable Member
Memorable Member

Re: Calculating Variance: Multiple Years on a table

In order for the user to control 2 different dates, it's probably best to have 2 different date tables.  (I could imagine also using a single table and using MIN / MAX to choose the years, but that feels weird to me).

 

Then you are sorta doing "sales, as filtered by date table 1" and "sales, as filtered by date table 2", then another measure to compare those.

 

 

View solution in original post

5 REPLIES 5
scottsen Memorable Member
Memorable Member

Re: Calculating Variance: Multiple Years on a table

We are going to need more info.  For starters - do you have a separate date table, connected to your raw data by date?

 

At any rate, you will find useful patterns on  Period Comparisons here:

http://www.daxpatterns.com/time-patterns/

 

Re: Calculating Variance: Multiple Years on a table

I merged the date table within my raw data table. I have columns that pull out the year, month, and day from the original date column. This seems to have always worked just as much as a separate date table would. 

 

I know the formula for variance, and I could easily create the measure if there were just 2 years, but I'm unsure how to do it with multiple years.

scottsen Memorable Member
Memorable Member

Re: Calculating Variance: Multiple Years on a table

Your examples are all comparisons to the prior year (2015 vs 2014)... is that always the case, or do you mean possibly 2016 vs 2014?

Re: Calculating Variance: Multiple Years on a table

Whatever two years that the user chooses; I want there to be a variance Column that shows what the pecentage between those years are on the table.

 

If there is an easier solution to show the variance, other than a table, I'm willing to try it. 

scottsen Memorable Member
Memorable Member

Re: Calculating Variance: Multiple Years on a table

In order for the user to control 2 different dates, it's probably best to have 2 different date tables.  (I could imagine also using a single table and using MIN / MAX to choose the years, but that feels weird to me).

 

Then you are sorta doing "sales, as filtered by date table 1" and "sales, as filtered by date table 2", then another measure to compare those.

 

 

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors