How can I calculate Spend YTD and Spend LY YTD with a full year Calendar table?

[dehmos27]

I have a model consisting of two sample tables: Spend and Calendar. The Spend table has two columns: Event Date and Spend.

The calendar table starts with 1/1 of the earliest Event Date’s year and ends with 12/31 of the latest Event Date’s year. As an example, the table extends from 1/1/2018 to 12/31/2020.

I want to calculate Spend YTD up until Today’s date and then the same period for the previous year. I also need Today to reflect the selected year in a slicer, so if 2019 is selected, today would be 4/20/2019 and last year’s cutoff would be 4/20/2018.

Any help would be greatly appreciated.

[amitchandak · Accepted solution]

You can use time intelligence for that with date calendar

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

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/

Or like

YTD Sales =
var _max =today()
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=_max)

Last YTD Sales = =
var _max =date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=_max)

[Ashish_Mathur]

Hi,

Try these measures

Total spend = SUM(Spend[Spend])

Spend YTD = CALCULATE([Total spend],DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),TODAY()))

Spend SPLY = CALCULATE([Spend YTD],SAMEPERIODLASTYEAR(Calendar[Date]))

Hope this helps.