Like for Like (LFL) Calculation

[AL16Derek]

The like-for-like sales comparison is an adjusted metric that compares two time periods, restricting the comparison to products or stores with the same characteristics. The like-for-like comparison only evaluates those stores that were open in the same periods.

I am attached a sample where
Store A opened in Jan 2019; Store B opened in Feb 2019; Store C opened in Jun 2019.

	Like for like Sales in Year 2019
	Jan	Feb	Mar	Apr	May	Jun	Jul
Store A	1000	2000	1500	3000	4000	2000	7000
Store B	0	1000	2000	1000	3000	3000	7000
Store C	0	0	0	0	0	4000	3000

When calculating Like for like sales in 2020, the expected result is as below

	Like for like Sales in Year 2020
	Jan	Feb	Mar	Apr	May	Jun	Jul
Store A	1000	1500	1000	2000	7000	1000	2000
Store B	0	2000	4000	3000	7000	2000	1000
Store C	0	0	0	0	0	1000	3000

Any way can do it in Power BI?

[v-yetao1-msft]

Hi @AL16Derek 

Please correct me if I wrongly understood your question.

(1)Enter the data that you provided and display as a Matrix visual in desktop .

Like this:

(2)Then create a Calendar Date table as a slicer,Time level to Year .

(3)Create a relationship between the two tables .And when you choose 2019, the data displayed is the data of 2019.

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AL16Derek]

Hi @v-yetao1-msft 
Nope. My query is how to create like for like calculation. 
Below is the raw data or refer (https://drive.google.com/drive/folders/1Wo46WhaugF9Tj8C7vpMBfKXe3gJJRW2d?usp=sharing)

As you can see, Store B & C are not opened in the beg of year. When calculating like for like sales in 2020, store B should ignore Jan sales while store C should ignore Jan-May sales.

[amitchandak]

@AL16Derek , Create measure like this with help from date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

use month, year in visual and slicer from Date table

create a measure like this

if(isblank([last year MTD Sales]), blank(), [MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[AL16Derek]

The MTD and LY MTD sales shows the same with the solution above. Wouldn't you mind checking the file and provide the fix?
https://drive.google.com/drive/folders/1Wo46WhaugF9Tj8C7vpMBfKXe3gJJRW2d?usp=sharing