CODEWITHSUNDEEP
excelpowerpivotdax
Manoj
Manoj

Reputation: 51

DATESYTD : Only constant date value is allowed as a year end date argument

I was trying to generate a YTD Report for sales with previous year comparison. We are following a Fiscal calendar. So it is necessity to use the second parameter of DATESYTD to pass the fiscal year end date.

But it seems like DATESYTD accepts only constant date as second parameter, like "12-01" or "06-01".

The problem here is we have a whole set of fiscal years with different end date, so when i try to pass a measure or do a switch case it is giving me the following error.

=CALCULATE (
SUM (Customers[quantity]),
DATESYTD (DimDate[Date], SWITCH([fiscal_year],
2007,"12-30",
2008,"12-28",
2009,"12-27",
2010,"12-26",
2011,"12-25",
2012,"12-30",
2013,"12-29",
2014,"12-28",
2015,"12-27"
)),
ALL (DimDate)
)

Only constant date value is allowed as a year end date argument.

The above mentioned fiscal year is used as a slicer, so i was thinking of passing the corresponding constant to the DATESYTD, but with no luck.

Is there any way to pass the values dynamically, i have heard about dynamic constant but have no idea about it. Any examples would be great.

Thanks in advance.

Upvotes: 0

Views: 3385

Answers (2)

James Hinton
James Hinton

Reputation: 1

The optional Fiscal Year end date has to be a constant.

If you attempt to point to DimDate[FiscalYearEndDate] you will receive the following error message:

Only constant date value is allowed as a year end date argument.

I have yet to find a solution for any of the YTD functions where the fiscal [YearEndDate] is end of February and any of the current or comparative years is a leap year.

Upvotes: 0

user401093
user401093

Reputation: 186

Try TOTALYTD.

TOTALYTD(,[,][,])

TotalYTD allows you to perform your summing, your date dimension, a filter, but also a FY ending date, which can either be a date you provide, or, a pointer to the date dimensions fiscal year column.

So for example: 

=TOTALYTD(SUM([Customers[Quantity]),DimDate[Date],DimDate[FiscalYearEndDate])

Upvotes: 1

Related Questions