Forum Discussion

A_a_a's avatar
A_a_a
Helper III
2 years ago
Solved

Comparing any years/quarters

Hi All,

 

I have a problem to do the comparison between any year with quarters view. When I want to compare year vs. any year, it is ok, but when I need to add quarters to a matrix table, the output is incorrect.

The system keeps Comp data as full year. Not sure why.

Please see pictures below.

 

 

There are two dimension tables with dates and also I added separate table for Quarters.

Measures for Base 1 and Base 2

Base 1 = sum(ABC[xyz])
Comp 1 = calculate([Base 1],all('Date1'),USERELATIONSHIP('Date to Comp'[Date],ABC[Date]))

 

What should I do to see correct data at Quarters Level? Please support.

Need Help
  • A_a_a's avatar
    A_a_a
    2 years ago

    Hi, what I did:

     

    I left all measures as I had but added quarters to matrix from ABC table not from dim or Date.

    Now, it is working well.

5 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Hello! I would suggest using a dynamic date table that will allow you to have many fields at your finger-tips, in that case, you could then simply create a YoY measure and have it apply when you are are the quarter level. Only the one date table would be needed, then you would make a measure your Base 1, then Base1 LY by utilizing the SAMEPERIODLASTYEAR DAX function, then a quick meaure for YoY%. For your viz you would filter to the current year, then add in the measures for Base1, Base1 LY, and Yoy%. Here is a blog I wrote about a date table which has teh link to the date table I love to use.

    http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

    • A_a_a's avatar
      A_a_a
      Helper III
      2 years ago

      Hi audreygerred 

       

      Thank you for that.

      Above there is only data sample, in my final project I will have to compare 5 years - so 5 years in Year 1 filter and 5 years in Year 2 comparison filter. Will your logic still be ok?

      • audreygerred's avatar
        audreygerred
        Super User
        2 years ago

        Yes.... for example, if you have a measure for Base1, Base1 LY, and YOY% and a slicer/filter on year and you filter to 2023, then Base1 will show value for 2023, Base1 LY will show for 2022, and YoY% will compare 2023 to 2022.

         

        If you change the filter to 2020, Base1 will show value for 2020, Base1 LY will show value for 2019 and YoY% will be comparison of 2020 and 2019. 

         

        The way it works is the measure itself is knowing what to look at based on the filter you have. So, in the measure for LY the SAMEPERIODLASTYEAR function 'looks' at the year that is filtered by the user and give you the value for that same period of last year (so if you have Q1 of 2023 filtered, Base1 LY will show Q1 of 2022 for the value).

  • A_a_a's avatar
    A_a_a
    Helper III
    2 years ago

    Hi, what I did:

     

    I left all measures as I had but added quarters to matrix from ABC table not from dim or Date.

    Now, it is working well.