cancel
Showing results for
Did you mean:
Highlighted
Helper V

## 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
Highlighted
Community Support

## Re: Find the Variance of Yearly Value

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.

Regards,

Xiaoxin Sheng

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

## Re: Find the Variance of Yearly Value

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.

Regards,

Xiaoxin Sheng

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors