Previous Fiscal Year YTD Sales Calculation Not Working

[rbowen]

I'm creating a basic executive dashboard showing total sales by fiscal year for FY22 and FY23 for numerous department categories. Our fiscal year runs from September 1st to August 31st. The model is pretty straightforward, just a sales table containing dates and amounts, and a calendar table with all the necessary columns (dates, fiscal months, month numbers, etc. I have a single cross filter relationship between the calendar and sales tables:

Calculating the current fiscal year, I'm using the following DAX: 

YTDSales2 = totalytd([TotalSales2], 'Calendar'[Date], ALL('Calendar'), "08/31")

This gives me the correct sales data for the various departments from 9/1/2022 thru today (June 26th). I need FY22 sales data for the same categories for the same period in FY22, 9/1/2021 thru 6/26/2022. Figuring this should be a simple SAMEPERIODLASTYEAR function, I'm using this DAX for the FY22 YTD calculation for fical year 22:

CALCULATE([YTDSales2],SAMEPERIODLASTYEAR('Calendar'[Date]))

Instead of getting the sales data for the correct YTD range, I'm getting all of FY22 sales data for each category. 

Using category FUEL as an example, what I should see for FY22 Sales is $84,507,646 but am getting $109,780,654 - the total FUEL sales for all of FY22 (9/1/2021 - 8/31/22). I've tried a variety of different ways of calculating FY22 sales for comparing to the same period of FY23 but all of them give the incorrect results. I've used these formulas in the past and they've worked but, for some reason, I can't get them to work now. Any ideas on what I'm missing?

Thank you

[amitchandak · Accepted solution]

@rbowen , Try like

YTD Sales = CALCULATE([TotalSales2],DATESYTD('Calendar'[Date],"08/31"))
Last YTD Sales = CALCULATE([TotalSales2],DATESYTD(dateadd('Calendar'[Date],-1,Year),"08/31"))
This year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR('Calendar'[Date]),"08/31"))
Last year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Date],-1,Year)),"08/31"))

YTD =
var _max = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

Make sure, date calendar table is marked as date table

Why Time Intelligence Fails - Powerbi 6 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[rbowen]

Thank you Amit. The results of the measures you suggested:

Last YTD Sales = CALCULATE([TotalSales2],DATESYTD(dateadd('Calendar'[Date],-1,Year),"08/31"))

Still gives an FY22 sales value that is too high and appears to be calculating the full sales value for all of FY22 instead of the YTD value. For example, the FUEL category sales value for FY22 YTD (9/1/2021-6/27/2022) should be $84,969,999 but the measure above has it showing as $109,780,654. 

Last year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Date],-1,Year)),"08/31"))

This measure gives a value that is much too low, only $48,918,547, and appears to be calculating the FUEL total from 9/1/2021 to 3/17/2022. 

LYTD =
var _max1 = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

This one calculates the correct value - with the [net] replaced with my [TotalSales2] measure. Seems like a bit of a convoluted way to calculate the previous fiscal YTD sales value but it works so who am I to complain. Thank you for your assistance.