Calculated Measure has incorrect grand total

[ChrisFromOhio]

Hello - I want to calculate YOY sales for various companies, but with different end dates. For example:

I have Company A sales data from 1/1/22 - 5/31/22, so prior year sales should be from 1/1/21 - 5/31/21.

I have Company B sales data from 1/1/22 - 6/15/22, so prior year sales should be from 1/1/21 - 6/15/21.

I have Company C sales data from 1/1/22 - 5/31/22, so prior year sales should be from 1/1/21 - 5/31/21.

I have sales data for companies A, B, and C from 1/1/21 - the dates listed above all in one combined query.

In order to prevent PY sales for companies A and C being calculated for 1/1/21 - 6/15/21, the formula I am using is as follows

IF(
    'SALES'[Sales $ Sum]<>BLANK(),
    IF(
        HASONEVALUE('Calendar'[Day]),
        SUMX(VALUES('SALES'[RETAILER]),
        CALCULATE(
            'SALES'[Sales $ Sum],
            SAMEPERIODLASTYEAR('Calendar'[Date])
        )),
        SUMX(VALUES('SALES'[RETAILER]),
        CALCULATE(
            'SALES'[Sales $ Sum],
            DATESBETWEEN(
                'Calendar'[Date],
                MIN('Calendar'[Date])-365,
                MAX('SALES'[Date])-365
            )
        )
    )
)
)

This works to get the total PY sales by retailer, accounting for retailer B having more days of sales, but in the grand total it is summing up to is taking all sales 1/1/21 - 6/15/21, when it should be leaving out sales 6/1/21 - 6/15/21 for retailers A and C. Any idea how to correct this?

[ChrisFromOhio · Accepted solution]

After playing with this issue, I ended up creating a new measure that calculates the max date for each retailer, and then replacing that in the bottom half of my formula. The measure now reads 

IF(
    'SALES'[Sales $ Sum]<>BLANK(),
    IF(
        HASONEVALUE('Calendar'[Day]),
        SUMX(VALUES('SALES'[RETAILER]),
        CALCULATE(
            'SALES'[Sales $ Sum],
            SAMEPERIODLASTYEAR('Calendar'[Date])
        )),
        SUMX(VALUES('SALES'[RETAILER]),
        CALCULATE(
            'SALES'[Sales $ Sum],
            DATESBETWEEN(
                'Calendar'[Date],
                MIN('Calendar'[Date])-365,
                'SALES'[Sales Data Thru]-365
            )
        )
    )
)
)