cancel
Showing results for
Did you mean:
Regular Visitor

## Need to calculate YOY using Quarter-Month field

Hi,

I have two fields in the table: Quarter and Sales.

The quarter field contains the value like Q2 2017, Q1 2017, Q4 2016, Q3 2016.

And Sales column has sales figures.

I have slicer which has Quarter Field as drop down. I need to calculate YoY for sales based on the selected Quarter from filter slicer.

For example, if the user has selected Q1 2017 in slicer from the quarter field, I need to calculate the sum of sales for Q1 2017 and sum of sales for Q1 2016 and then calculate YoY.

Please guide me to achieve this.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Need to calculate YOY using Quarter-Month field

Hi @Musketeers,

You can create a Calendar Table, and create Quarter-Month column in the table. Create a relationship betweem them. You can create the current and last year using DATEADD function.

```LastValue = CONCATENATE(LEFT(Table6[Quarter],3),VALUE(RIGHT(Table6[Quarter],4))-1)

Last = LOOKUPVALUE(Table6[Sales],Table6[Quarter],Table6[LastValue])```
```Current=SUM(Table[Quarter])
LastYear=CALCULATE(SUM(Table[sale]),DATE(Calendar[Date],-1,Year))```

Another solution, I create the following sample table.

Create the calculated column using the formulas.

```LastValue = CONCATENATE(LEFT(Table6[Quarter],3),VALUE(RIGHT(Table6[Quarter],4))-1)

Last = LOOKUPVALUE(Table6[Sales],Table6[Quarter],Table6[LastValue])```

Create a slicer including Quarter, select sum(Table[Sale]), sum(Table[Last]) as value, please see the screenshot below.

Best Regards,
Angelia

2 REPLIES 2
Microsoft

## Re: Need to calculate YOY using Quarter-Month field

Hi @Musketeers,

You can create a Calendar Table, and create Quarter-Month column in the table. Create a relationship betweem them. You can create the current and last year using DATEADD function.

```LastValue = CONCATENATE(LEFT(Table6[Quarter],3),VALUE(RIGHT(Table6[Quarter],4))-1)

Last = LOOKUPVALUE(Table6[Sales],Table6[Quarter],Table6[LastValue])```
```Current=SUM(Table[Quarter])
LastYear=CALCULATE(SUM(Table[sale]),DATE(Calendar[Date],-1,Year))```

Another solution, I create the following sample table.

Create the calculated column using the formulas.

```LastValue = CONCATENATE(LEFT(Table6[Quarter],3),VALUE(RIGHT(Table6[Quarter],4))-1)

Last = LOOKUPVALUE(Table6[Sales],Table6[Quarter],Table6[LastValue])```

Create a slicer including Quarter, select sum(Table[Sale]), sum(Table[Last]) as value, please see the screenshot below.

Best Regards,
Angelia

Regular Visitor

## Re: Need to calculate YOY using Quarter-Month field

Hi Angelia,

Since the second solution is looking good...so I will go with the second solution.

Thanks for your time and help.

Thanks

Announcements

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

And the winner is...

#### Announcing the New Spanish Forum

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

#### 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

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors