Forum Discussion
Calculate Prior Year Sales When Different IDs Have Different Ends Dates
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.
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.
From your sample data it looks like you already implemented the best possible option.
What else do you need to improve?
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.
In that case you would have to sever the link to the calendar table, and use the explicit measures.