Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Aikarauta
New Member

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

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])
1 ACCEPTED SOLUTION
gpoggi
Responsive Resident
Responsive Resident

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

View solution in original post

1 REPLY 1
gpoggi
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.