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:

 

 

 

The dax for YTD (correct dax) is:

 

YTD AC = 

CALCULATE(
    SUM(
        'Query1 (AC)'[[Units])
            ,DATESYTD(
                  'Query1 (AC)'[Date])
                  )

 

And for PY (not calculating correctly) is:

 

PY YTD AC = 


CALCULATE(
    SUM(
        'Query1 (AC)'[[Units]),
        SAMEPERIODLASTYEAR(
            Query1[Date]
        )
)

 

Any idea how to fix?

 

Thanks.

 

Need Help
Show and Tell

10 Replies

  • Power_Track100's avatar
    Power_Track100
    New Member
    2 years ago

    Hi o59393,

    Please find the below solution:

     

    Measure 1=

    YTD =
    CALCULATE(
        SUM('My table'[Units]),
        DATESYTD('My table'[Date])
    )

    Measure 2=

    YTD LY =
            CALCULATE (
                [YTD],
                SAMEPERIODLASTYEAR('My table'[Date])
    )




     









     

     



    ********************Please accept this as a solution***************************

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

      Hi Power_Track100 

       

      I got values repeated:

       

       

      Dax used:

       

      PY YTD AC = 


CALCULATE(
    [YTD AC],
        SAMEPERIODLASTYEAR(
            Query1[Date]
        )
)

       

       

      Just in case, my dates are always 1/1/2023, 2/1/2023, 3/1/2023.... 2/1/2024

       

      Appreciate your help

  • 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.