topic Re: Ytd in DAX Commands and Tips

Ytd

Anonymous — Mon, 15 Aug 2022 08:40:10 GMT

Hi,

I know this might be an easy question, but I can't figure out.

I'm using this dax for getting all 2019 vaue.

2019 value=calculate('value'[total],DATEDD('Calendar'[Date],Calculate((YEAR(MAX('Calendar'[Date])) - 2019)*(-3)),YEAR))

and i want to calculate the ytd of 2019 by using dax below.

YTD 2019 = TOTALYTD('value'[2019 value],'Calendar'[Date])

I have a date slicer, from 2019 to 2023.

I wish to show the 2019 value when i press 2022 in the date slicer.

How should I modify the dax for 2019?

I will also need to calculate the percentage difference of 2019 and the year i select ytd.

Thanks for your help.

Re: Ytd

ValtteriN — Mon, 15 Aug 2022 10:30:19 GMT

Hi,

Since you are calculating different years values with 2022 in the date slicer I recommend anchoring the date for your YTD. So something like this should do the trick:

2020 ytd =

var _sdate= DATE(2020,1,1)

var _cdate = MAX('Calendar'[Date])

var _edate = IF(_cdate>DATE(2020,12,31),DATE(2020,12,31),_cdate)

return

CALCULATE(SUM(Cumulativetotal[Value]),all('Calendar'),ALL(Cumulativetotal[Date]),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

This way the mesure will always return 2019 even if the date slicer has values from 2019-2022.

Here is an example of this:

Data:

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Ytd

Anonymous — Mon, 15 Aug 2022 11:26:14 GMT

Hi ValtteriN,

That's working for bringing the value for entire 2020-2023, but are there any chance to using sameperiodlastyear or other dax formula that Jan to Dec will same as 2020 ytd Jan to Dec. Because the output value now is only the 2020 Dec value bring down to 2023.

Thanks for your help.

Re: Ytd

ValtteriN — Tue, 16 Aug 2022 06:39:11 GMT

Hi,

You could add the month as a variable like this:

2020 ytd =

var _sdate= DATE(2020,1,1)

var _cdate = MAX('Calendar'[Date])

var _month = month(MAX('Calendar'[Date]))

var _edate = IF(_cdate>DATE(2020,_month,31),DATE(2020,_month,31),_cdate)

return

CALCULATE(SUM(Cumulativetotal[Value]),all('Calendar'),ALL(Cumulativetotal[Date]),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

Re: Ytd

Anonymous — Wed, 17 Aug 2022 07:16:03 GMT

Hi ValtteriN,

After I adding thet code, that still same.

I wish to copy 2020 all value to 2021 which easier to make comparison between original 2021 value.

Thanks for your help.