Forum Discussion
Doing LFL Calculations
Hi nick_mk,
If you mean create YOY measure to calculate with previous period, you can refer to below links:
Custom Year-Over-Year Calculation in DAX
Regards,
Xiaoxin Sheng
Sorry no, not really.
Most of the business world use like for like calculations in doing YoY variances. Like for Like means you only include products/stores (depending what you are comparing) that appearred in both years. Any data from new stores or old stores is disregarded.
In excel/sql this task is relatively simple. You essentially lookup/inner join between 2 datasets to only pull information applicable in both.
I have a long winded way of doing it by creating 2 calculated fields per variance - 1 to determine if the product/store was available/open the previous year and another to determine what the last year value was if it was available/open. Frankly it's disappointing and I might as well add the value columns myself in SQL, but I thought I wouldn't have to duplicate data in Power Bi.
Also I work to a 4-4-5 calendar which is standard across the UK for retailers. This means I can't apply a MoM comparison directly. I have apply weekly averages and use a measure. This is ok but as the data is no longer at row level, the calculation can only be use din limited ways.