Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
goutham
Frequent Visitor

LFL Calculations - YOY comparison using SamePeriod Last year function

Hi Everybody ,

 

 

I am trying to calulate LFL year on year growth . but getting incorrect results as the sameperiodlastyear function applying the logic on LFL flags in transaction as well. 

 

For example i have following data 

 

Store              YEAR           LFL Status   REVENUE

Store 12016NLFL50
Store 22016NLFL50
Store 12017NLFL100
Store 22017LFL100
Store 32017LFL100
Store 42017LFL100
Store 12018LFL150
Store 22018LFL150
Store 32018LFL150
Store 42018NC180

 

 

LFL - Like for like , NLFL - Not like for like ( new store in previous year),NC -Non comparable (expansion or revovation happened)

 

i am using Sameperiodlastyear to calculate previous revenue. 

 

for filter 2018 and LFL - i have store 1 and 2 and 3. but the previous year revenue is shown only for store 2 and 3 but not store 1 because store 1 was NLFL in 2017. i am unable to filter the data with LFL falg at same time have the sameperiodlastyear provide values for all the mentioned stores.

 

could anybody help in how to tell sameperiodlastyear to check only stores selected this year and get the lastyear.

 

 

Thanks

4 REPLIES 4
goutham
Frequent Visitor

Adding to above ,

 

using measure Revenue PY = CALCULATE(SUM(REVENUE),SAMEPERIODLASTYEAR(DATETABLE[Date]),ALL(LFL Status))

works fine at Store level.  ( If use a table with store , revenue and revenue PY columns)

 

But if I take out Store and try to see at year level in KPI visual this again gives incorrect result explained above.

 

kindly help if you have come across this scenario.

@goutham,

 

You can use PREVIOUSYEAR() instead and check if it can meet your requirement:

Revenue PY =
CALCULATE (
    SUM ( DATETABLE[REVENUE] ),
    PREVIOUSYEAR ( DATETABLE[Date] ),
    ALL ( DATETABLE[LFL Status] )
)

Community Support Team _ Jimmy Tao

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

@v-yuta-msft , 

 

thank you for the reply !

 

No unfortunately even this function behaves the same way .  I have made few changes at data level to handle this . created  few flags.

 

This is working perfect. I shall share the details soon.

 

 

Regards

Goutham Dasari

@goutham ,

 

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.