An author's initial Like-for-Like (L4L) solution for retail store comparisons produced technically correct prior-year (PY) values but confused users. For stores with temporary or permanent closures, the current period and PY measures assigned different L4L states, making results appear inconsistent. Users expected both measures to align under the same state, revealing a gap between technical accuracy and practical understanding.

Power Query couldn't resolve this because it required comparing dates against two different year contexts simultaneously. The fix involved writing a SQL cursor procedure to generate two separate keys: L4LKey (based on PY dates) and L4LKey_PY (based on same-year dates). Processing stores sequentially by closing date prevented duplicate rows and correctly mapped each month's status for both perspectives.

In Power BI, a new relationship connected the bridge table's L4LKey_PY to the dimension table. The Retail Sales (PY) measure then used USERELATIONSHIP() to activate this second path. This ensured PY values fell under the identical L4L state as current-year sales. The exercise underscores that solution design must prioritize user mental models over purely technical correctness.