Forum Discussion

alexcatala's avatar
alexcatala
Helper IV
5 years ago

LFL Conversion from 2 different tables

Hi,    I am currently working on a formula to update my LFL Conversion.   The problem I am finding is the current formula there is only 1 (Weekly figures) table where I extract the Transactions a...
Need Help
alexcatala's avatar
alexcatala
Helper IV
5 years ago

Hi, 

 

I am currently working on a formula to update my LFL Conversion.

 

The problem I am finding is the current formula there is only 1 (Weekly figures) table where I extract the Transactions and also the Footfall.

Now on the new one, I would like to have it from 2 different Tables: Transactions will be from the table (DailyPlan) and Footfall from (DailyFootfall).

 

Here there is a sample of data from DailyPlan

 

Here there is a sample of data from DailyFootfall

 

 

How I can change the 1st part of the formula, as Addcolumns only allows me to take from 1 table?

 

VAR BoP_Date_Fixed = [BoP Date]
VAR f_WeeklyFigures_Enriched =
ADDCOLUMNS (
FILTER(f_WeeklyFigures,f_WeeklyFigures[Measure] = "Transaction count"),
"Include", MAX (
[COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
[NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
)
,"Footfall",
VAR LY_SKWeek = RELATED((d_DateTable[Year_WeekNum]))
VAR ItStore = f_WeeklyFigures[Store]
Return
CALCULATE ( SUM ( f_WeeklyFigures[Value] ),
ALL ( f_WeeklyFigures ),
ALL ( d_DateTable ),
f_WeeklyFigures[Measure] = "Footfall TY",
f_WeeklyFigures[Store] = ItStore,
d_DateTable[Year_WeekNum] = LY_SKWeek
)
)
VAR f_WeeklyFigures_Enriched_Filtered =
Filter(f_WeeklyFigures_Enriched, [Include] = 1)
VAR Transactions =
SUMX (
f_WeeklyFigures_Enriched_Filtered,
if([value]<>0 && [Footfall]<>0,[value])
)
VAR Footfall =
SUMX (
f_WeeklyFigures_Enriched_Filtered,
if([value]<>0 && [Footfall]<>0,[Footfall])
)
VAR Result = DIVIDE(Transactions,Footfall)
RETURN
IF ( Result = 0, [ND_Sign], Result )

 

 

Any suggestion?