topic YTD Year over Year in DAX Commands and Tips

YTD Year over Year

SK16 — Sat, 25 Apr 2020 13:40:18 GMT

Hi Guys,

I an facing some issue in Dax.

I want to calculate Year over Year Change for YTD. Like for Feb'20 i have data till feb so I want in YOY change it should select data for 2019 till feb only.

But somehow it is taking taking Full year 2019 data.

Can you pls help me out here.

Re: YTD Year over Year

amitchandak — Sat, 25 Apr 2020 13:50:30 GMT

SK16

This will work with date calendar.

If you have slicer to pass dates

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))

Using Today

YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Using Max date

YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

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/

I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L

Re: YTD Year over Year

Mariusz — Sat, 25 Apr 2020 13:51:43 GMT

Hi SK16

You can add an If condition to check if the fact table "sales" in my case is empty.

Sales YTD = IF( NOT ISEMPTY( Sales ), CALCULATE( [Sales], DATESYTD( 'Calendar'[Date] ) ) )

Sales YTD LY = CALCULATE( [Sales YTD], SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: YTD Year over Year

SK16 — Sat, 25 Apr 2020 22:24:12 GMT

Hi Mariusz,

Thanks for your help buddy.

I got the issue which I am facing in my Dax i.e if if dont select any Month then it is taking Full Previous Year but if I any select months from the slicer then it is showing the correct value.

But I want without selecting any month i should consider lates month for YTD.

Re: YTD Year over Year

SK16 — Sat, 25 Apr 2020 22:26:42 GMT

Hi Mariusz,

Thanks for your help buddy.

I got the issue which I am facing in my Dax i.e if I dont select any Month then it is taking Full Previous Year but if select months from the slicer then it is showing the correct value.

But what I want is that without selecting any month it should consider latest month for YTD.

Re: YTD Year over Year

amitchandak — Sun, 26 Apr 2020 06:13:38 GMT

SK16 , check the way I suggested to force either Today or max date as a filter in YTD.

You have to force additional date filter on top of YTD

Re: YTD Year over Year

SK16 — Thu, 30 Apr 2020 08:40:26 GMT

Thanks Amit,

It really helped

Re: YTD Year over Year

SK16 — Thu, 30 Apr 2020 08:46:21 GMT

I am facing same issue for Month to date.

Can you please help me out here.