Previous Fiscal Year (TOTALYTD)

[swwong1]

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?

File can be downloaded from: https://www.dropbox.com/s/qjkkp78vp4ttmoc/TOTALYTD.pbix?dl=0

Thanks!

[OwenAuger · Accepted solution]

Hi @swwong1 

Uploaded edited pbix here.

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

[swwong1]

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

[v-piga-msft]

Hi @swwong1 ,

I 'm a little confused about your requirement.

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

Best Regards,

Cherry

[swwong1]

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