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

Relative Cumulative variance

Hi all,

 

I have the following two measures for cumulative sales amounts for a fiscal year, with the previous year plotted for comparison:

 

YTD = CALCULATE(SUM(FactInternetSales[SalesAmount]), FILTER (ALL(FactInternetSales), FactInternetSales[Fiscal Year] <= MAX(FactInternetSales[Fiscal Year]) ))


PYTD = CALCULATE(SUM(FactInternetSales[SalesAmount]), FILTER (all(FactInternetSales),FactInternetSales[Fiscal Year]= MAX(FactInternetSales[Fiscal Year])-1 &&  (FactInternetSales[OrderDate]) <= MAXX(FactInternetSales,DATEADD(FactInternetSales[OrderDate],-1,YEAR))))

 

 

I would like to have a seperate visual showing the variance in % terms within the same period.

 

Variance = ( [YTD] - [PYTD] ) / [YTD] doesn't work.

 

 NB: The Fiscal Year is April to March in this instance.

 

 

Can anyone help??

 

Rumbles

2 ACCEPTED SOLUTIONS

Accepted Solutions
Rumbles Frequent Visitor
Frequent Visitor

Re: Relative Cumulative variance

Ok, soI've solved the problem, by dealing with the infinity calculation at the beginning, by using:

 

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],0)

 

 

BUT: the graph shows 0% for the first year of trading for which there was no previous year.

Is there a way of the third argument returning something other than 0?

 

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],"") would work,but I can't then format the measure as a percentage, since it contains text.

 

 

Any ideas?

 

Rumbles

v-huizhn-msft Super Contributor
Super Contributor

Re: Relative Cumulative variance

Hi @Rumbles,

1. You don't need to use "" if you want to display blank() on the picture. If don't add any argument on the DIVIDE function, the default value is BLANK(). 

2. Personnal, I think 1 is the apporpriate like the followinf formula. There is no previous year, so the increase should be by the current year self. It will return on the graph.

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],1)


Please let me know if you have any question.

Best Regards,
Aneglia

4 REPLIES 4
Super User
Super User

Re: Relative Cumulative variance

When you say it doesn't work, what kind of error are you getting?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Rumbles Frequent Visitor
Frequent Visitor

Re: Relative Cumulative variance

I've got slightly further now.

 

Of course, it should be  (YTD - PYTD) /PYTD to get the increase from PYTD.

 

But, on the visual the lines are missing, but when I hover over, I get the information. See attacheda2.PNGa1.PNG

Rumbles Frequent Visitor
Frequent Visitor

Re: Relative Cumulative variance

Ok, soI've solved the problem, by dealing with the infinity calculation at the beginning, by using:

 

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],0)

 

 

BUT: the graph shows 0% for the first year of trading for which there was no previous year.

Is there a way of the third argument returning something other than 0?

 

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],"") would work,but I can't then format the measure as a percentage, since it contains text.

 

 

Any ideas?

 

Rumbles

v-huizhn-msft Super Contributor
Super Contributor

Re: Relative Cumulative variance

Hi @Rumbles,

1. You don't need to use "" if you want to display blank() on the picture. If don't add any argument on the DIVIDE function, the default value is BLANK(). 

2. Personnal, I think 1 is the apporpriate like the followinf formula. There is no previous year, so the increase should be by the current year self. It will return on the graph.

Increase From Previous Fiscal Year ii = DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD],1)


Please let me know if you have any question.

Best Regards,
Aneglia