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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.