topic Re: DATESYTD not working ? in DAX Commands and Tips

DATESYTD not working ?

admin_xlsior — Mon, 06 Dec 2021 18:26:26 GMT

Hi guys,

Can DATESYTD working without any context ?

I have this data like :

Is having this kind of measure :

All net sales YTD = CALCULATE( [All net sales], DATESYTD(Dates[Date]) )

works ?

If based on my table, shouldn't I receive a total of net sales for year of 2021 ? April - Sept 2021 ? Because currently it is blank.

If I click on the row of that table, for example April 1 2021, then the measure is correct, returned the exact value (87,800), but when none selected which is what I want because I only need the total of sales "up-to-this-year" which mean I do not need that table, then it returned blank.

Thanks

Re: DATESYTD not working ?

Mikelytics — Mon, 06 Dec 2021 20:10:41 GMT

HI admin_xlsior ,

For DatesYTD you need date filter context.

Measure

without fitler context

with filter context

other filter context (here 2020 is ignored since I also chose something from 2021)

________________________

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

Click on the Thumbs-Up icon if you like this reply.

Re: DATESYTD not working ?

ValtteriN — Mon, 06 Dec 2021 20:37:20 GMT

Hi,

If I understood your problem correctly you would like to have YTD measure in a KPI card or something similar to that? Based on the problem description I think your slicer selection is not working as you would hope and thus you get the blank value. Instead of Datesytd you could use the following approach:

My test data:

Ideal outcome:
So in this example the goal is that without any slicers the YTD measure would return 350. That is this year's running total until today. If something is selected the calculation should be done until the selected day. So for example If I select 4.12.2021 the calculation would be from this time period: 1.1.2021-4.12.2021.

Measure using datesbetween ([Base measure] is just SUM of [Value]):

YTD from base =

var test = HASONEFILTER(YTD_Example[Date])

var _sdate = if(test,DATE(YEAR(SELECTEDVALUE(YTD_Example[Date])),1,1), DATE(YEAR(TODAY()),1,1))

var _edate = if(test,MAX(YTD_Example[Date]),TODAY()) return

CALCULATE([Base measure],DATESBETWEEN(YTD_Example[Date],_sdate,_edate))

End result:
Without selection (calculates until today):

With date selected (calculates until selection:

Hopefully this can be used to resolve your issue and if it does accept this as a solution!

Re: DATESYTD not working ?

VahidDM — Mon, 06 Dec 2021 21:26:57 GMT

Hi admin_xlsior

You need to add a Calendar [Date] table to your model to use time intelligence DAX functions.

Use this link to add that calendar: https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: DATESYTD not working ?

admin_xlsior — Tue, 07 Dec 2021 03:19:32 GMT

Hi both,

Thanks, I thought it does not need context for this particular function, but it does. Initially I do not intent to have any slicer for this time.

ValtteriN , this is so cool.

Thanks