Forum Discussion

nick_mk's avatar
nick_mk
Frequent Visitor
8 years ago

Doing LFL Calculations

I'm a bit stuck...

 

I'm trying to do a LFL measure based on week/month/quarter/year using a 4-4-5 custom calendar. I need it at location level, but obviously, I want to aggregate it up to site,region level. Every time I aggregate the measures I have created they forget it is supposed to be at location level and the LFL calc gets aggregated to site or region. Measures work on the fly so it isn't surprising, however, is there another way to do LFL?

5 Replies

  • nick_mk's avatar
    nick_mk
    Frequent Visitor
    8 years ago

    I'm a bit stuck...

     

    I'm trying to do a LFL measure based on week/month/quarter/year using a 4-4-5 custom calendar. I need it at location level, but obviously, I want to aggregate it up to site,region level. Every time I aggregate the measures I have created they forget it is supposed to be at location level and the LFL calc gets aggregated to site or region. Measures work on the fly so it isn't surprising, however, is there another way to do LFL?

      • nick_mk's avatar
        nick_mk
        Frequent Visitor
        8 years ago

        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.