Like for Like comparison

[ZakyQadir]

Hi,

I need your help on this issue, I need to find Like For Like (YoY comparison) between two full period. 

I have 3 table.
Sales Table
List Data Table
Calendar Table

I try to make comparison use sameperiodlast year but total are not equal with detail.
Like for like comparison only apply for full month, so if site open on 29/03/2023, it will start comparison on 01/04/2024 vs 01/04/2023.
Aplicable for every new site opened (Have several site) - Based on opening date

Current Measures i use i try to have another table for each stores and flag it with LFL but still no work.

_Sales TY LFL = CALCULATE(SUM(Sales[Sales]),FILTER(_StoreFlagLFL,_StoreFlagLFL[FlagLFL] = "LFL"))

_Sales LY LFL = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateCalendar[Date]))

Thanks in advance for your help

Expected Result

SiteCode	Date	TY Sales	LY Sales	% Growth	TY LFL Sales	LY LFL Sales	% LFL
A001	29/03/2024	20.000	20.000	0%
A001	30/03/2024	30.000	30.000	0%
A001	31/03/2024	15.000	15.000	0%
A001	01/04/2024	60.000	40.000	50%	60.000	40.000	50%
A001	02/04/2024	70.000	50.000	40%	70.000	50.000	40%
A001	03/04/2024	80.000	60.000	33%	80.000	60.000	33%
		275.000	215.000	28%	210.000	150.000	40%

List Data Table

SiteCode	OpenDate
A001	29/03/2023
A002	01/01/2022

Sales Table

SiteCode	Date	Sales
A001	29/03/2023	20.000
A001	30/03/2023	30.000
A001	31/03/2023	15.000
A001	01/04/2023	40.000
A001	02/04/2023	50.000
A001	03/04/2023	60.000
A001	29/03/2024	20.000
A001	30/03/2024	30.000
A001	31/03/2024	15.000
A001	01/04/2024	60.000
A001	02/04/2024	70.000
A001	03/04/2024	80.000

[Dangar332]

Hi, @ZakyQadir 

I apologize, but I didn't understand your situation
can you elaborate what kind of output you get from above measures and where you stuck in there ?

[ZakyQadir]

Hi, 

I need result like expected result, currently im stuck like this. Last year sales calculated since opening date, bu i need full month as base so first few days not count as LFL basis.

Current situation

SiteCode	Date	TY Sales	LY Sales	% Growth	TY LFL Sales	LY LFL Sales	% LFL
A001	29/03/2024	20.000	20.000	0%		20.000
A001	30/03/2024	30.000	30.000	0%		30.000
A001	31/03/2024	15.000	15.000	0%		15.000
A001	01/04/2024	60.000	40.000	50%	60.000	40.000	50%
A001	02/04/2024	70.000	50.000	40%	70.000	50.000	40%
A001	03/04/2024	80.000	60.000	33%	80.000	60.000	33%
		275.000	215.000	28%	210.000	215.000	-2%

[Greg_Deckler]

@ZakyQadir First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[ZakyQadir]

Yes, but my knowledge not that good, i need your help about that, my measure wrong maybe there's another solution from the experts

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Dangar332  and @Greg_Deckler  offered, and i want to offer some more infotmation for user to refer to.

hello @ZakyQadir , you can refer to the following sample.

sample data is the same as you privded, i create a calendar table, and create relationship among tables.

Create the following measures.

Sales = SUM(Sales[Sales])

StartofMonth =
VAR a =
    MAX ( 'List Data'[OpenDate] )
VAR b =
    EOMONTH ( a, -1 ) + 1
VAR c =
    EOMONTH ( a, 0 ) + 1
RETURN
    IF ( [Sales] <> BLANK (), IF ( b = a, b, c ) )

TY LFL Sales =
VAR a =
    EDATE ( [StartofMonth], 12 )
VAR b =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        OR (
            YEAR ( b ) = YEAR ( [StartofMonth] )
                && b >= [StartofMonth],
            YEAR ( b ) = YEAR ( a )
                && b >= a
        ),
        [Sales]
    )

LY LFL Sales = IF([TY LFL Sales]<>BLANK(),CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))

% LFL = DIVIDE([TY LFL Sales]-[LY LFL Sales],[LY LFL Sales])

Then put the measures to the visual.

Best Regards!

Yolo Zhu

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

Output

[ZakyQadir]

It works really well, thanks a lot for your support...