Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

YTD vs Previous YTD

Hi all   I have the following table:     I am doing the sum of YTD and I am trying to compare it with the same period of last year (2023) but the values for PY YTD are wrong:     ...
Need Help
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

Try this approach

  1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
  2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
  3. To your visual/filter/slicer, drag Year and Month name from the Calendar Table
  4. Select an year in the slicer
  5. Write these measures
Total = SUM('Query1 (AC)'[[Units])
Total YTD = CALCULATE([Total],DATESYTD(Calendar[Date],"31/12"))
Total YTD SPLY = calculate([Total YTD],sameperiodlastyear(calendar[date]))

Hope this helps.

  • o59393's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Ashish_Mathur 

     

    Your measures worked, but if I click on the 2024 year, the PY values wont appear on the column "PY YTD AC"

     

     

    How can I reflect the sales for Jan 2023 and Feb 2023 next to the Jan 2024 and Feb 2024 rows of the table intead of blanks?

     

    The other thing that caught my attention was the the matrix on the right says the following:

     

     

     

    I attach pbix:

    sample_sameperiod.pbix

     

    Thanks a lot.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      You have not followed any of the steps which i mentioend in my previous post.  I do not see any Calendar Table at all.  Read and implement properly.

      • o59393's avatar
        o59393
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        Hi Ashish_Mathur 

         

        Sorry about that, here is the pbix updated.

         

        I created the calendar table with the columns you mentioned from my power query.

         

        Still not showing the PY value and the matrix gives the error.

         

        Can you please have a look? sample_sameperiod.pbix

         

        Thanks.