YTD vs Previous YTD

[o59393]

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.

[Ashish_Mathur]

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]

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]

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]

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.

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[o59393]

Solved, thanks @Ashish_Mathur  !!

[Ashish_Mathur]

You are welcome.

[Power_Track100]

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]

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

[Power_Track100]

@o59393 could you please sahe a sample file. I think this will help to support you?