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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
QHY
New Member

Create Measure Amount with dynamic end of fiscal year using DATESYTD

Hi, 
 

     I am trying to create a measure of Amount YTD. 


     The date of end fiscal year is dynamic( it used 4-4-5 and/or 4-4-6 rule). 

     In Dates table, each row of date had FiscalStartDate and FiscalEndDate.  


     My question is  I careted the Measure Amount YTD like

     "Measure Amount YTD = CALCULATE([Measure Amount],DATESYTD('Dates'[Date], FiscalEndDate ))". 

      The power bi showing:"Only constant date value is allowed as a year end date argument."


 I understand it is not right to create the Measure Amount YTD like this.  Is there anyone have any sucessfull solution?

 

Best Regards,

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @QHY ,

 

Creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. 

Please check if below links are helpful.

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ 

https://community.powerbi.com/t5/Desktop/Setup-a-custom-calendar-and-calculate-YTD-PTD-etc/td-p/1524... 


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

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

Hi @QHY ,

 

Creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. 

Please check if below links are helpful.

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ 

https://community.powerbi.com/t5/Desktop/Setup-a-custom-calendar-and-calculate-YTD-PTD-etc/td-p/1524... 


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

Hi Liang,

 

Following the approaches that you provided, the problem was successfully solved. Thank you so much. Your prompt response saved us a lot of time.

 

Best Regards,

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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