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

Find the Variance of Yearly Value

Hi, 

 

I am wanting to create a visualization that shows the total value of sales per month for this year and last year, and also the variance. 

 

Could someone help me out with the steps I would take to achieve this succesfully?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Find the Variance of Yearly Value

Hi @ThePowerBIQueen,

 

You can use PREVIOUS YEAR function to get the previous year value.

 

Tables: DateTable(Date), Fact(ID,DATE, VALUE)

Relationship: Date to Date(One to many), both cross filter direction.

 

Measures:

Get previous year value.

PY = CALCULATE(SUM('Fact'[Value]),SAMEPERIODLASTYEAR('DateTable'[Date]))

 

 

Calcualte the diff between current year and previous year

Diff = [PY]- SUM('Fact'[Value]) 

 

 

Create a matrix visual to display the result:

DateTable[Date] to Rows, switch to hierarchy mode and keep year and month, Fact[VALUE] and two measures to Values fields.

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Find the Variance of Yearly Value

Hi @ThePowerBIQueen,

 

You can use PREVIOUS YEAR function to get the previous year value.

 

Tables: DateTable(Date), Fact(ID,DATE, VALUE)

Relationship: Date to Date(One to many), both cross filter direction.

 

Measures:

Get previous year value.

PY = CALCULATE(SUM('Fact'[Value]),SAMEPERIODLASTYEAR('DateTable'[Date]))

 

 

Calcualte the diff between current year and previous year

Diff = [PY]- SUM('Fact'[Value]) 

 

 

Create a matrix visual to display the result:

DateTable[Date] to Rows, switch to hierarchy mode and keep year and month, Fact[VALUE] and two measures to Values fields.

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |