Calculate Prior Year Sales When Different IDs Have Different Ends Dates

[ChrisFromOhio]

Time calculations with Power BI are the bane of my existance.

I have a dataset with sales by item, by day, with multiple retailers. These retailers all have differing end dates for data. For example, I might have the following:

Retailer A sales 1/1/21 - 12/31/23, with sales reported at a monthly level (i.e. all of January 2021 sales will report under 1/31/21).

Retailer B sales 1/1/21 - 2/6/24, with sales reported at a daily level.

Retailer C sales 1/1/21 - 1/31/24, with sales reported at a monthly level.

My PBI report attempts to align everything to a true YOY view, so that if I am looking at 2024 YTD, I will only see the following sales:
This Year: Retailer B 1/1/24 - 2/6/24 plus Retailer C 1/1/24 - 1/31/24
Last Year: Retailer B 1/1/23 - 2/6/23 plus Retailer C 1/1/23 - 1/31/23

Most measures I've created just compare 2024 sales to Retailer A, B, and C 1/1/23 - 2/6/23, which is obviously incorrect since Retailer A and C do not have sales yet for certain dates in this year's time frame.

The closest I've come is creating the below measure, but it is not without its flaws:

Sales_Prior YTD = 
IF(
    [Sales]<>BLANK(),
    IF(
        HASONEVALUE('Calendar'[Date]),
        SUMX(VALUES('Sample Data'[RETAILER]),
        CALCULATE(
            [Sales],
            SAMEPERIODLASTYEAR('Calendar'[Date])
        )),
        SUMX(VALUES('Sample Data'[RETAILER]),
        CALCULATE(
            [Sales],
            DATESBETWEEN(
                'Calendar'[Date],
                MIN('Calendar'[Date])-365,
                MAX('Sample Data'[Date])-365
            )
        ))
    )
)

 For example, if an item did not have sales in a month the year prior and I am looking at item level sales, I will see only a blank for that month this year. If I have sales last year but not this year, that line item does not show up.

Is there an easier way to calculate YTD sales when there are varying degrees of end dates to consider? Dropbox link below with sample dataset.

Sample PBI (Dropbox) 

[ChrisFromOhio]

Is there any other solution to this problem?

[lbendlin]

There's no magic to them there time "intelligence"  calculations *. You have to implement it in the way that is applicable for your business scenario.

* Ok, the end-of-february trickeries _are_ impressive.

[lbendlin]

The closest approximation you can get to is to peg the monthly and yearly reports to an arbitrary date (for example first day in the month or in the year).  And then link that to your calendar table which you use for the time "intelligence"  functions.

[ChrisFromOhio]

But I would like to be able to view this report through the month, when other retailers are providing sales throughout the month, to get a true YOY view, rather than have to wait for all retailers to align to an arbitrary date.

[lbendlin]

From your sample data it looks like you already implemented the best possible option.  

What else do you need to improve?

[ChrisFromOhio]

See above. If an item did not have sales in a month the year prior and I am looking at item level sales, I will see only a blank for that month this year. If I have sales last year but not this year, that line item does not show up. This was only a sample dataset, but the original one has far more retailers and items.

[lbendlin]

In that case you would have to sever the link to the calendar table, and use the explicit measures.