Hi,

is there a way to create parameterized FYTD and LFYTD measures and avoid hardcoding fiscal year end date in the DATESYTD function ?

I have tried using both VALUES and LOOKUPVALUE to retrieve fiscal year end date inside DATESYTD, but it apparantly only accepts a literal.

I have created a Calendar table with e.g. FiscalYearIndex which is 0 for current fiscal year dates and was thinking somthing with CALCULATE and a filtered Calendar table based on FiscalYearIndex = 0.

Best regards,

Jesper Bork

Hi Jesper,

The fiscal year end date is supposed to be fixed. For DATESYTD and TOTALYTD you only have to provide the day and month for the end date, and you can use that for any year in your data. So if your reporting context is July 2015, DATESYTD with end date "31/12" will return all dates from 1/1/2015 until 31/7/2015. If your report context is April 2014, the result will be all dates from 1/1/2014 until 30/4/2014.

Hi Jesper,

Have you already considered using a variation of the custom time intelligence approach described here: http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/?

Hi Jseper,

According to your description, you need to create parameterized FYTD and LFYTD measures, right?

Generally, for the Fiscal Year to Date, we can create a date table with all dates from before your min date to somewhere after your max date. Then create calculate column and measures. The sample DAX expression below is for you reference.
FiscalMonthNumber:=IF('Date'[Month] <= 6,'Date'[Month] + 6,'Date'[Month] - 6)
Total:=SUM(TABLE[COLUMN])
Total_YTD:=CALCULATE([Total],DATESYTD('Date'[date],"6/31"))

Here is a thread which similar to your, please refer to the link below.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/525c8a24-63a9-4b4e-8689-340ce7e41856/fiscal-year-to-date

If this is not what you want, please provide us more information, so that we can make further analysis.

Regards,