YTD Sales by Fiscal Year and Filter by Month

[Be-ro92]

Hello,

I am trying to create a measure where I can calculate the YTD sales for a fiscal year starting in Oct-YYYY. I would then like to select a month within the fiscal year and have it roll up the total sales for any previous months.

My issue is that using the DATESYTD function tries to look sum data from the most recent calendar year. But I only want to show the YTD for the fiscal year.

For example. If calendar year 2023 data is available and I filter my visual by "December" then the DATESYTD function will try to find sales December 2023 and will end up being blank. 

By selecting December, I only want to the YTD sales to calculate up to the current FY 2022 December and ignore anything from calendar year 2023.

Date	Monthly Sales	YTD Sales
Oct-22	$100	$100
Nov-22	$200	$300
Dec-22	$500	$800
Jan-23	$50	$850

[ghoshabhijeet · Accepted solution]

Hi@Be-ro92,  Thanks for your query.

You can use TOTALYTD function instead of DATESYTD. While using the TOTALYTD function, you can define the end month of your fiscal year.

For example:
Total YTD Sales = TOTALYTD([Total Sales], FiscalYearCalendar(Date), "10,31")

This would help you calculate the YTD values till the selected month on your slicer.

For any further assistance, please share the pbix file. Thanks!

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍