Aggregated Current Year vs. Previous Year with Date Range Slicer

[cisenberg]

I'm creating a report using a matrix visual with Rows set to a product line hierarchy (division->line->item) and values including Invoiced Sales, Units, the previous year values for both, and the deltas between the two.

I'm using a date slicer where the user is able to pick the date range. The idea is that in the same canvas, a user could look at YTD (1/1/2019 - 7/26/2019), MTD (7/1/2019 - 7/26/2019), or previous years (1/1/2018 - 7/26/2018).

Initially, I was using this bit of DAX to shift my max and min dates (set by the slicer) back one year each:

PY Sales = 
VAR CurrentDateMax = MAX(‘Sales Orders’[INVOICE DATE])
VAR CurrentDateMin = MIN(‘Sales Orders’[INVOICE DATE])
VAR EarliestDate = MIN(DateDim[Date])
VAR PYDateMax = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate,
                DATE(YEAR(CurrentDateMax)-1, MONTH(CurrentDateMax), DAY(CurrentDateMax))
)
VAR PYDateMin = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate,
                DATE(YEAR(CurrentDateMin)-1, MONTH(CurrentDateMin), DAY(CurrentDateMin))
)
RETURN
CALCULATE(
    SUM(‘Sales Orders’[Invoiced Sales $]),
    FILTER(
        ALL(‘Sales Orders’[INVOICE DATE]),
        ‘Sales Orders’[INVOICE DATE] >= PYDateMin &&
        ‘Sales Orders’[INVOICE DATE] <= PYDateMax
    )
)

Here's my issue:

In this particular example, the product division level ($214k) is just wrong, overreporting by ~$7k. The product line level ($207k) is underreporting by a little over $125. In other examples, the grand total is correct, while individual product lines don't add up to the total.

This almost seems like an AutoExists issue (refer to this excellent article from sqlbi), but my Sales Order, Item, and Date tables are separate, so I didn't think this behavior should be invoked.

I'm completely stumped as to why this is happening, and I'm not really sure how to move forward without completely redesigning my report. A lot of the functionality from the report comes from the user being able to arbitrarily decide on a date range and then see the delta from there. Any help would be appreciated.

[Anonymous]

Mate, be so kind, please, and share the pbix file. You can share it via Google Drive or OneDrive. Just don't forget to set the permissions right. OneDrive is preferable. Dropbox will do as well.

Thanks.

Best

Darek

[cisenberg · Accepted solution]

IT granted me access to share via OneDrive:

Sample PBIX

That being said, I believe that I've figured out the problem and solution in the interim. Long story short, I actually used my DateDim table to filter the canvas instead of INVOICE_DATE, and I modified my measure to simply use SAMEPERIODLASTYEAR(); all of the preliminary validation I've done confirms that this approach works. This was infinitely easier than the approaches I've tried before, and it's a good example of why you want to appropriately model your data and then use the model properly!

The detailed explanation is that when I used INVOICE_DATE as a filter (and SALES_CCN), since these fields are both in the same table as my [Invoiced Sales $], my measure invoked DAX Autoexists via the CALCULATE() function. Effectively, this excluded sales of items in 2018 that were not sold in 2019, thus why we see no PY sales in the old method. Per the excellent article I referenced in my original post, there is no way to bypass Autoexist other than to filter a table using fields from other tables, and for that, you need a proper star schema.

I appreciate the help on this; creating the sample file got me thinking in the right direction.