LY sales by LFL status, using current year day LFL status

[Anonymous]

First time post.

Interested in tips on how like for like sales are computed in DAX and BI visuals.  I have a fact table with days, departments and sales.  I've used a manual table identfying non like for like status, added through Power Query, to assign one of four LFL statuses (LFL, New, Closed, Closures) to each line of the fact table.  Assignment is based on comparing day LFL status to day -364 LFL status.

Visual is correctly showing sales by day/week and shop categorised by LFL status for current year, but when you calculate last year for comparative (DATEADD -364 days) the status picks up the index for the last year date not current.  Current year might be LFL but last year NEW.

I tried REMOVEFILTERS on the LFL dimension table, it shows last year sales in every LFL status column as expected.  How to either get it to show only where shop and date has current year sales, ignoring prior year LFL status in relevant line on the fact table, or alternative?

[Anonymous]

Found a solution using Dax, but welcome comments on how to optimise this because it runs a little slow

[vazmor]

Hello, I have the same problem, but different model. You Help me.

Re: Calculate Same store or not same store - Microsoft Power BI Community

[Anonymous]

Hello, I've not been able to allocate time to speed up this approach, but believe there may be an adaptable solution here https://www.daxpatterns.com/like-for-like-comparison/

[vazmor]

Hi, Thanks, if I do these steps but it doesn't give me the correct result either, I still get the filter.

In your formula you have the following tables, these tables as you have them, that is, what fields do you have there.

dLFLCompare
dLFLstatus

Sales Last = 
VAR NumberOfYears =
     CALCULATE (
         DISTINCTCOUNT ( Dim_Date[Currency_Date] ),
         CROSSFILTER ( Fact_Sales[Id_Date], Dim_Date[Id_Date], BOTH ),
         ALLSELECTED ( )
     )
VAR StoresAndYears =
    CALCULATETABLE (
        SUMMARIZE (                    -- Group the Receipts table
            Fact_Sales,                            -- by store and year
            Dim_Store[Id_Store],
            Dim_Brand[Id_Brand],
                                  -- in order to count how
            Dim_Date[Currency_Date]      -- many years a store is present in
        ),                             -- 
        ALLSELECTED ( )                -- Over all selected years and stores
    )
VAR StoresAndYearCount =
     GROUPBY (
         StoresAndYears,
         Dim_Store[Id_Store],
         Dim_Brand[Id_Brand],              
         "@Years", SUMX ( CURRENTGROUP (), 1 )
     )
VAR OpenStores =
    FILTER (
        StoresAndYearCount,
        [@Years] = NumberOfYears 
    )

VAR Result =
    CALCULATE (
        [AmountLY],
        KEEPFILTERS ( OpenStores )     -- Filters Store[StoreKey] 
    )
RETURN
   Result

[Anonymous]

Will look at this next https://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/

[vazmor]

Hello, I have already verified everything I have made my own dax, it still does not come out, it does not filter me, could you help me just with an example of your model, with which you applied the solution dax.