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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors