BUG in YTD formula? Very urgent

[Anonymous]

Hello,

It is already a week that I want to use a YTD formula. I can not find the solution:

I have a table with these coloms:

date (in dateformat) - target (in € format) - Seller (in text format)

Every first of the month they get a target.

eg: Seller X has to sell 150 000 euro each month

I want to see what the YTD target is en I used: 

CALCULATE (SUM(Table[target];DATESYTD(Table[date])) -> and I get te sum of the target for a whole year (so 12 x 150 000)

Than I tried:

TOTALYTD (Table[target];DATESYTD(Table[date])) -> Same Result

So why oh why does my YTD do not give me the YTD-dates but all the dates!

I only have 6 houres left to get finish my report 😞

[Anonymous]

Give us a graphic representation pls... in a excel show us what you have and what is your expected result. 

[Anonymous]

There is no bug in DATESYTD.

DATESYTD(<DateColumn>,<YearEndDate>) does not work on current system date. It works on the current context. i.e. if your target table contains the dates up to end of the year. It will pick up the last date available in the table and calculates the date range unless you filter out certain months.

For example, if your financial starts on 1st of April and ends on 31st of March, then DATESYTD(Calendar[Date], "3-31") will return the dates beginning from 1st April to the last date on your table's date column. In your case, the table contains the targets up to the end of the year. So it is always calculating the target for the whole year. But if you add a slicer to select months up to the current month, your formula will show the results correctly.

To determine the current system date and show the dates correctly, you have to modify the formula as follows...

Present Formula

CALCULATE (
		SUM(Table[target]),
		DATESYTD(Table[date])
	)

Modified Formula 

CALCULATE (
		SUM(Table[Target]),
		FILTER('Table',MONTH('Table'[Date])<=MONTH(TODAY()))
	)

The formula shown above has a MONTH() formula in it. This is to address the following scenario...

The current date is 15.05.2019 and your May target's date is entered as 31.05.2019. In this scenario, if you don't use Month() formula and just write Target[Date] <= Today(), then it will show targets only up to 30.04.2019 and it will exclude 31.05.2019 because it's greater than Today(). But if you match Month(15.05.2019) and Month(31.05.2019) then May targets will also get included in your YTD figure.

The concept is this. You may achieve the same results using other date functions like DATESBETWEEN(), STARTOFTHEYEAR(), ENDOFTHEMONTH(), etc...

[Anonymous · Accepted solution]

BTW I forgot to mention that I assumed your target table has only current year's targets. If the table has historical target data, then you should add a starting date also to the filter.

[Anonymous]

thank you, you are my hero