YTD Calculations Across Mutliple Years

[Anonymous]

Hello, I've been trying to find a way to show YTD comparisons across multiple years for about a week.  My approach to doing this is to create a YTD flag column in Power Query.  I can do this very easily in EXCEL and really hope someone can show me the equivalent DAX formula(s) to do the same in Power BI.  Below shows what my data looks like (in grey) and my method to flagging YTD months across years (in excel).  I'd be very grateful if someone could show me how to do this using DAX/Power Query!

Thanks!

Jordan

[amitchandak]

You can use time intelligence functions, you need to have date dimension for that, You need to select a date to restrict it

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"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
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))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

12/31 is the year end you can change as per need.

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Also, this calculation does not bring back any values @amitchandak  You would think that almost everyone that uses POWER BI would have to do YTD comparisons.  Crazy how difficult it is...

[amitchandak]

YTD needs a date context. If there is no date selected it will go till the end of the calendar and calculate from there. And if selected some date and you want complete last year then use

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

[Anonymous]

I would just like YTD last year, so if the latest month is June of this year, it will calculate Jan - June of previous year and so on....I would like to create this without having to update a date each month upon data refresh.  By date selected, do you mean "12/31"? ...  I would like it to just measure through the latest month in the latest year....then apply that month to previous YTD calculations, automatically @amitchandak 

[amitchandak]

that "12/31" is year-end date. Do you have a date slicer? because if do not select a date. Then date of ytd and lytd will be based on end date of calendar

[Anonymous]

I would like to calcuate YTD across multiple years without using manual filters and slicers.  That is why my initial approach was to create a flag like I did in my excel example to flag YTD months. @amitchandak 

[amitchandak]

 you plot for multiple year it should work. Can you share sample data and sample output.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Thanks @amitchandak  

Do you know how to fix this error I'm getting with my attempt?

[amitchandak]

put ) before ,dateytd

Sum is not closed after measure name

[Anonymous]

What about this one? @amitchandak 

[amitchandak]

Remove one after Date[Ddate] and move at last

datesytd(Date[Date],"12/31") )//one of calculate