Replacing <year_end_date> in DATESYTD with a measure, is it possible?

[Aikarauta]

Hello folks,

is it possible to make <year_end_date> parameter to be dynamic? I have been trying to replace that parameter with a measure. That way, I would only have to update one measure, instead of having to manually type my year end date to each YTD formula in my model. This is tedious to do as I am utilizing the same model for different datasets. Every time a company uses different fiscal year than the standard one, I will have to update various formulas to match their fiscal year.

However, I have been not able to come up with such a measure. The error message I get is that year end parameter has to be a constant. If I were to define my year end measure like this:

yearEndMeasure := "30/6", it still won't work.

Any ideas of how to accomplish this? Thanks!

DATESYTD(<dates> [,<year_end_date> <--- MEASURE GOES HERE])

[gpoggi · Accepted solution]

Hey Aikarauta,

As far as I know, unfortunately it's not possible to make it dynamic 😞 so what I suggest is to create your own DATESYTD function, like this:

MY_YTD = 
VAR FiscalYearEndMonth = 1
VAR EndDate = MAX('Calendar'[Date])
VAR StartDate = IF(
DATE(YEAR(EndDate),FiscalYearEndMonth+1,1) < EndDate,
DATE(YEAR(EndDate),FiscalYearEndMonth+1,1),
DATE(YEAR(EndDate)-1,FiscalYearEndMonth+1,1)
)
RETURN
CALCULATE(<Your Measure>,DATESBETWEEN('Calendar'[Date],StartDate,EndDate))

So you can set the variable "FiscalYearEndMonth"  to the month number of your Fiscal Year End Month, basically 1 means '01/31/2020', 2 means '02/29/2020' and so on...

Depending of your data model, I don't know if it would require a little effort to apply it but you can give it a try 😅

Any question, just let me know.

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

Regards,

Gian Carlo Poggi