Forum Discussion
Help Calculating Previous Fiscal Year Sales
Greetings All - I need to calculate previous fiscal year to date sales data by sales category based on our our fiscal year. It starts September 1st, ends August 31st. I have my sales table relat...
Bmejia
Super User
Super UserIf you not resolved yet here is another option but it would not be dynamic.
Create a column on you calendartable if you not already have that provides the fiscal year offset date (you will need a Year & FullMonth Name or change the full name below to short month name)
CurrentFiscalOffset = var _today = TODAY()
var MonthValue = SWITCH([MonthLong],"January",1,
"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)
var _cur_year = YEAR( _today)
return
SWITCH(TRUE(),
OR([Year]=_cur_year && MonthValue<=8, [Year]=_cur_year-1 && MonthValue>=9),0,
OR([Year]=_cur_year && MonthValue>=9, [Year]=_cur_year+1 && MonthValue<=8),1,
OR([Year]=_cur_year-1 && MonthValue<=8, [Year]=_cur_year-2 && MonthValue>=9),-1
)
Then add a measure that always looks at previous fiscal year, -1 equals previous Year
CALCULATE (
CALCULATE (
[Total Sales Amount],
FILTER('CalendarTable','CalendarTable'[CurrentFiscalOffset]="-1"),
SAMEPERIODLASTYEAR ( 'Date'[Date] )
Thank Bmejia, I do have a date table with a number of columns for doing various fiscal year related operations:
As I mentioned in a reply to Alex_Sawdo, I have the report mostly working at this point. However, I'll do some experimenting with the DAX you provided to see if it helps. Thank you again for all of your suggestions, much appreciated.