cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Previous Fiscal Year (TOTALYTD)

Hi All

I am trying to use TOTALYTD to calculate the YTD sales, with my fiscal year ending at 30/06. Using the above formula should cover the period from 1/7/2019 onwards.

I am looking to calculate the previous year (which should cover 1/6/2018 to 30/06/2019) using the formula:

Total Sales FY (Previous Year) = totalytd([Total Sales],Dateadd(DateTable[Date],-1,YEAR),"30/06")

However, it doesn't seem to calculate as I expected, would someone please advise?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Previous Fiscal Year (TOTALYTD)

Hi @swwong1

1. For certain time intelligence functions to work properly, the DateTable needs to contain complete fiscal years. In your case, extending the calendar table to at least 30 June 2020 will should the immediate issue. Here is one way you could do this:
```DateTable =
VAR YearEndMonth = 6
VAR MonthShift = 12 - YearEndMonth
VAR MinDatePlus6Months =
EDATE ( MIN ( DATA[Date] ), MonthShift )
VAR MaxDatePlus6Months =
EDATE ( MAX ( DATA[Date] ), MonthShift )
VAR CalendarStart =
EDATE ( DATE ( YEAR ( MinDatePlus6Months ), 1, 1 ), - MonthShift )
VAR CalendarEnd =
EDATE ( DATE ( YEAR ( MaxDatePlus6Months ), 12, 31 ), - MonthShift )
RETURN
CALENDAR ( CalendarStart, CalendarEnd )```
Or check out https://www.sqlbi.com/tools/dax-date-template/
2. I also noticed that Total Sales Fiscal Year referred to DATA[Date], but you should use DateTable[Date]:
`Total Sales Fiscal Year = TOTALYTD([Total Sales],DateTable[Date],"30/06")`

Hopefully that fixes the issues. Please post back if needed

Regards,

Owen

Proud to be a Datanaut!

4 REPLIES 4
Community Support Team

## Re: Previous Fiscal Year (TOTALYTD)

Hi @swwong1 ,

What is the start date for a year based on your scenario? "1/1" or "6/1"?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Previous Fiscal Year (TOTALYTD)

Apologies typo.
Previous year fiscal period should be 1.7.2018 to 30.6.2019
Super Contributor

## Re: Previous Fiscal Year (TOTALYTD)

Hi @swwong1

1. For certain time intelligence functions to work properly, the DateTable needs to contain complete fiscal years. In your case, extending the calendar table to at least 30 June 2020 will should the immediate issue. Here is one way you could do this:
```DateTable =
VAR YearEndMonth = 6
VAR MonthShift = 12 - YearEndMonth
VAR MinDatePlus6Months =
EDATE ( MIN ( DATA[Date] ), MonthShift )
VAR MaxDatePlus6Months =
EDATE ( MAX ( DATA[Date] ), MonthShift )
VAR CalendarStart =
EDATE ( DATE ( YEAR ( MinDatePlus6Months ), 1, 1 ), - MonthShift )
VAR CalendarEnd =
EDATE ( DATE ( YEAR ( MaxDatePlus6Months ), 12, 31 ), - MonthShift )
RETURN
CALENDAR ( CalendarStart, CalendarEnd )```
Or check out https://www.sqlbi.com/tools/dax-date-template/
2. I also noticed that Total Sales Fiscal Year referred to DATA[Date], but you should use DateTable[Date]:
`Total Sales Fiscal Year = TOTALYTD([Total Sales],DateTable[Date],"30/06")`

Hopefully that fixes the issues. Please post back if needed

Regards,

Owen

Proud to be a Datanaut!

Frequent Visitor

## Re: Previous Fiscal Year (TOTALYTD)

Thanks @OwenAuger

I will read up on point 1 as I usually just use the Minimum and Maximum year, taking months from Jan and Dec

Since my example was fiscal year, I had to take maximum year + 1 to have complete fiscal years

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 266 members 2,770 guests
Recent signins: