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 relat...
Need Help
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.