Difference between Sum of This YTD vs Last YTD

[darentengmfs]

Hi,

I am trying to compare the sum of this YTD sales vs the sum of last YTD's sales. I've looked at a number of solutions and tried the function SAMEPERIODLASTYEAR and it does not work as it requires all the dates to be inserted in the chart.

What I'm trying to achive is to put the difference of the YTD (this year - last year) in a single card.

For example, This YTD amounts to 5,000, Last YTD amounts to 4,800. The card should just show +200.

Thanks!

Daren

[v-jingzhang]

Hi @darentengmfs 

I created 3 measures to finally achieve the difference between YTD this year and last year as below:

Sales YTD = CALCULATE(SUM('Sales'[Sales]),DATESYTD('Calendar'[Date].[Date]))

Sales YTD LY = CALCULATE(SUM('Sales'[Sales]),DATESYTD(DATEADD('Calendar'[Date].[Date],-1,YEAR)))

Sales Difference YTD = Sales[Sales YTD] - Sales[Sales YTD LY]

Here is my sample data of ‘Sales’ table, and I used a ‘Calendar’ table.

And here is my test result. When select different month from the slicer, it will display corresponding YTD difference value in the card.

Additionally, you could refer to this similar question link https://community.powerbi.com/t5/Desktop/Difference-of-THIS-YEAR-YTD-and-LAST-YEAR-YTD/td-p/467676. It has another solution without using SAMEPERIODLASTYEAR function which may be helpful.

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it more quickly.

[amitchandak · Accepted solution]

@darentengmfs , try this with a date table and time intelligence

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

diff =[YTD Sales] - [Last YTD Sales]

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[rsbin]

Hi @darentengmfs 

Courtesy of Enterprise DNA:  https://www.youtube.com/watch?v=JNt-_QByeLk

I have been using his videos quite a bit lately and find them quite useful and educational.

Good Luck and Regards,

[AllisonKennedy]

You need to have a DimDate table for time intelligence functions to work.

I prefer using DATEADD for the Previous YTD and I prefer calling it Previous YTD rather than 'last' YTD because you really need to have a Year selected in order to ensure that it displays the YTD values for only 1 year (last year) otherwise it will return YTD SUM for all PREVIOUS years (except this current year).
Other option is to filter the card visual for this year. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html