Need to calculate YOY using Quarter-Month field

[Musketeers]

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.

[v-huizhn-msft · Accepted solution]

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.


Please feel free to ask if you have any other issue.

Best Regards,
Angelia

[Musketeers]

Hi Angelia,

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

Thanks for your time and help.

Thanks