cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
swwong1 Frequent Visitor
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?

 

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

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Previous Fiscal Year (TOTALYTD)

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 Smiley Happy

 

Regards,

Owen

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Community Support Team
Community Support Team

Re: Previous Fiscal Year (TOTALYTD)

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

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.
swwong1 Frequent Visitor
Frequent Visitor

Re: Previous Fiscal Year (TOTALYTD)

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

Re: Previous Fiscal Year (TOTALYTD)

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 Smiley Happy

 

Regards,

Owen

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




swwong1 Frequent Visitor
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

 

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 266 members 2,770 guests
Please welcome our newest community members: