Create Measure Amount with dynamic end of fiscal year using DATESYTD

[QHY]

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,

[V-lianl-msft · Accepted solution]

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.

[QHY]

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,