TotalYTD formula for different fiscal year

[jtgriffin]

Hello Everyone, 

I have an issue where my fical year is calculated incorrectly due to our fiscal year starting 10/1/2019 and ending 9/30/2020. 

I have a measure that is calcluating Actual (This Period), which is as follows:

Actual (TP) = SUM(Actuals[Amount (adj)])

This formula takes inputs from my actuals input tab (excel) and calculates for the current period. 

Now I have a Actual (YTD) formula, that ideally I would like to calculate from 10/1/2019 - 9/30/2020. However it is currently calculating from 1/1 to 12/31, normal fiscal year. Here is the formula:

Actual (YTD) = TOTALYTD([Actual (TP)],'Date'[date])

So that formula is taking my previous formula for actuals, Actual (TP), and trying to add them together using my date tab in excel. But currently I get 1/1 to 12/31 fiscal year outputs. 

My date table is posted below. Please let me know how I can fix this issues. Thank you!

[amitchandak · Accepted solution]

Total ytd can take year end

Actual (YTD) = TOTALYTD([Actual (TP)],'Date'[date],"9/30")

Actual (LYTD) = TOTALYTD([Actual (TP)],dateadd('Date'[date],-1,year),"9/30")

These are some datesytd example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"9/30"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/319/30"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"9/30"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"9/30"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[UdayReddy511]

Can I have similar syntax for QTD(Sep,Oct,Nov-Q1....) and HYTD(Sep,Oct,Nov,Dec,Jan,Feb,Mar-HYTD) (Fiscal Year starts from Sep-Aug)

[jtgriffin]

Thank you, I wasnt aware of that formula and it worked perfectly!