Help comparing two different periods using SAMEPERIOD()

[ArthurLuz]

Hi all! I've been stuck on this problem for a few weeks now, tried a few solutions I found online but couldn't solve the issue at hand.

I've been trying to compare the value of some KPIs between itself and the same KPI but on the previous year.

I already made my data to be in the Wide format, which I think is right, but I´m not being able to compare it to the previous year and use the headers the way I want. 

This is my data structure:

My quarter/Year are text, so I've read that would be good to arbitrary associate some data with it, I made a separate calendar table and used the RELATED function to make this "Coluna Data" calculation in my database. The logic is, for the 1rst quarter the month would be 01, 2nd quarter would be 02 and it goes on. For the full year I've put dates with the months as 12.

With this, I made three measures:

1) Total Atributes = sum('database'[Valor])

2) Total Atributes LY = calculate(Total Atributes, sameperiodlastyear('database'[Coluna Data])

3) % Diference = divide( total atributes - total atributes LY , total atributes LY, "No Previous Period)

It kinda works when I put everything inside a matrix:

But I do not want it to show the data numbers as the headers, I wanted it to show the Quarter/FY Names, like 1Q09 and 2009. 
I've tried two things here, putting in the headers/columns the values of the Quarter/Year column that I have in my database and I get an error in my Measure #2, it is blank and therefore the measure #3 doesn't work as well.

I've also tried to get as columns the values (1Q09 and etc) from the support database that has the relationship between the dates and the names, and also the order:

This is the database:

The are linked between the Quarter/Ano and also with both Dates columns

I belive that the problem might be with the sameperiodlastyear formula, but can I overcome this? Changing the measures?

I mean, I was thinking that I would be able to make this conection and that power Bi would understand that when I choose for example, the 1Q09 he would need to make the measures using the datas. But I'm not sure how to put it into my measure.

If there is a completely diferent way to do this that I'm not seeing, I would change anything gladly.

Thank you in advance!

[Ashish_Mathur]

Hi,

The Year and Quarter columns should be in the Calendar Table.  To your visual/slicer/filters, drag any date dimenion such as year, month, quarter from the Calendar Table.  Revised your measures to:

1) Total Atributes = sum('database'[Valor])

2) Total Atributes LY = calculate(Total Atributes, sameperiodlastyear(Calendar[Date]))

3) % Diference = divide([total atributes] - [total atributes LY],[total atributes LY])

Hope this helps.

[ArthurLuz]

Hi Ashish! Thank you for your reply.

I understood what you meant, I've tried it and couldn't make it work because if I drag the date dimension I wouldn't be able to use my Text columns. I tried to make a new column in my calendar with the text quarters/years and still couldn't do it.
But your post helped me see that the solution was good, I've made a duplicate of my database and left one of them with just the Full Year values/dates and another one with the quarters. This way I was able to sum the quarters and not have a full year + 4Q in my 4Q visual.

I had to divide things into two visuals for now, one with the comparisons between years and another one with quarters, but I will look for a way to integrate them into a single table/visual that changes if we want to see years or quarters.

So thank you very much!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

The Year and Quarter columns should be in the Calendar Table.  To your visual/slicer/filters, drag any date dimenion such as year, month, quarter from the Calendar Table.  Revised your measures to:

1) Total Atributes = sum('database'[Valor])

2) Total Atributes LY = calculate(Total Atributes, sameperiodlastyear(Calendar[Date]))

3) % Diference = divide([total atributes] - [total atributes LY],[total atributes LY])

Hope this helps.