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.

Reply
o59393
Post Prodigy
Post Prodigy

YTD vs Previous YTD

Hi all

 

I have the following table:

o59393_0-1709426277752.png

 

 

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:

 

o59393_1-1709426404841.png

 

 

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

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1709517244432.png

 


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

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
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/

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"

 

o59393_0-1709441798448.png

 

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:

 

 

o59393_1-1709441986161.png

 

I attach pbix:

sample_sameperiod.pbix

 

Thanks a lot.

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/

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.

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1709517244432.png

 


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

Solved, thanks @Ashish_Mathur  !!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Power_Track100
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])
)


Screenshot 2024-03-02 210335.png



 









 

 



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

Hi @Power_Track100 

 

I got values repeated:

o59393_1-1709432172327.png

 

 

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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