Following Up on Like-for-Like for Stores: Handling PY

An issue is identified in a Like-for-Like (L4L) retail sales solution where Previous Year (PY) calculations are technically correct but confusing for users. The problem occurs when a store's status, such as being temporarily closed, changes between years, causing PY sales to be misaligned with the current L4L state in reports. The proposed solution involves using a procedural SQL approach with a cursor to build a new bridge table that contains separate L4L keys for both the current period and the previous year. This new table is then imported into Power BI, and the USERELATIONSHIP DAX function is used to activate the correct relationship for PY calculations, ensuring results are consistent and intuitive from a user's perspective.