Forum Discussion

THar01's avatar
THar01
Frequent Visitor
3 years ago

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 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.

 

Need Help

10 Replies

  • Bmejia's avatar
    Bmejia
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      THar01
      Frequent Visitor
      3 years ago

      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's avatar
        Bmejia
        Icon for Super User rankSuper User
        3 years ago

        How about 

        PY = CALCULATE(SUM(Sales[Total Sales Amount]),DATESYTD(dateadd(Datetable[Date],-1,Year),"8/31"))
  • Bmejia's avatar
    Bmejia
    Icon for Super User rankSuper User
    3 years ago
    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's avatar
      THar01
      Frequent Visitor
      3 years ago

      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. 

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    3 years ago

    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's avatar
      THar01
      Frequent Visitor
      3 years ago

      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.