Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy

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

1 ACCEPTED SOLUTION
Super User

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
10 REPLIES 10
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Post Prodigy

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Post Prodigy

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.

Super User

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Post Prodigy

Solved, thanks @Ashish_Mathur  !!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
New Member

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***************************

Post Prodigy

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

New Member

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

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors