Help Calculating Previous Fiscal Year Sales

[THar01]

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 related to a date table and have created a measure to calculate the current FY23 fiscal year to date sales - 9/1/2022 to today (4/5/2023). This measure works correctly and I'm able to display the data in a standard table. However, when I try to calculate the previous year's fiscal year to date sales, I'm getting incorrect results. As a test, I created a table from our total sales column and manually contrained it to show sales from 9/1/2021 - 4/5/2022, and the numbers are correct. Here's what the FY22 sales data for that date range should be:

The correct current FYTD measure is:

YTDSales4 = CALCULATE(SUM('Sales'[Total Sales Amount]), DATESYTD('Sales'[Date Key],"08/31"))  yes, I'm using the dates in my sales table as trying to use the date table itself results in a completely blank table. No idea why, but this way works. 

I've tried two different measures to calculate previous fiscal year to date sales, both are giving the exact same incorrect results. The measures are:

- PYSales4 = Calculate([YTDSales4],SAMEPERIODLASTYEAR('DateTable'[Calendar Date]))

- PYSales5 = CALCULATE([YTDSales4],DATESYTD(dateadd('DateTable'[Calendar Date],-1,Year),"8/31"))

If I use the date table key, I get a completely blank table in both cases. In both cases, the resulting table looks like this. Note that the sales values are too low.

I know I've got either a function or some syntax messed up but don't know what it is. What am I missing?

Thank you.

[Alex_Sawdo]

A few questions that could help figure out why this isn't working for you:

1. What does the relationship between the sales table and date dimension table look like? In theory, it shoudl be between two date fields. If either field is a date/time data type, the table will most likely return blank values. You can check this by viewing the data type within power query for each table/column. 
2. You're on the right track for the dax, but another function you could use is the OFFSET function. An example would be this:

CALCULATE(

    CALCULATE(

        [Your Measure Here],

        OFFSET(

            -1,

            FILTER(

                ALLSELECTED(

                    [Your Fiscal Year Column]

                ),

                NOT ISBLANK([Your Measure Here])

            )

        )

    ),

    REMOVEFILTERS(

        [Your Date Column]

    )  

)

What this measure does is finds the value calculated from the previous data point, ignoring any filters placed on a date column. 

[THar01]

Hi Alex - 

Yes, you're correct, I do have an active relationship between the date table (Calendar Date)  and the Date Key column in the sales table. And for other calculated columns/measures in the report that relationship is functioning as expected. Still haven't figured out why it wasn't working for this particular operation. I inherited most of this report and there are/were many tangled columns/measures spanning about a dozen other tables so my guess is that may have something to do with this and other issues I've been dealing with.  I have the report mostly working at this point and the execs are fine with it as it is. I'd still like to get this figured out though as there'll be a need to perform these types of calculations in the future with other reports. Thanks for the DAX suggestion, I'll give that a try.

[Bmejia]

If 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 (

    [Total Sales Amount],

    FILTER('CalendarTable','CalendarTable'[CurrentFiscalOffset]="-1"),

    SAMEPERIODLASTYEAR ( 'Date'[Date] )

[THar01]

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. 

[Bmejia]

If you can provide a sample of your data.

[Bmejia]

Your Previous year should look something like this, It seem like your calling YTSales4 Again.
PY=CALCULATE(SUM('Sales'[Total Sales Amount]),SAMEPERIODLASTYEAR('DateTable'[Calendar Date]))

[THar01]

Thanks Bmejia, but that doesn't work either. This gives values that are much too large because I think it's not limiting the sales data to just the date range 9/1/2021 - 4/5/2022. 

[Bmejia]

How about 

PY = CALCULATE(SUM(Sales[Total Sales Amount]),DATESYTD(dateadd(Datetable[Date],-1,Year),"8/31"))

[THar01]

That measure doesn't throw any errors, but it still doesn't provide the correct values for the fiscal YTD sales for FY22. I get the exact same values that are too low that I showed above. Having a hard time understanding why a calculation that should be relatively straightforward is proving so difficult to do in PBI. Is there some row level context I'm missing in my DAX?

[grega]

Hi,

Was this resolved in the end?

Thanks