Sales comparison with LFL Status filter slicer

[Ann0]

Hello all,

I am new here & new to the Power BI just learning.

I am trying to develop a sales report that reflects the Sales comparison YoY with a slicer option to filter only the LFL stores (stores with sales same Weekday LY).

I try to calculate the LFL based on the bleow:
1. The data I use is from 01 Jan 2024 to 30 May 2025, so I need the LY data up to 30 May
2. Some stores have sales LY but closed this year which need to be excluded in LFL filter
3. Some new stores have sales in 2025 but not in 2024, This also I excuded.
4. I try to make the comparison basesd on the weekday not on the date 

This shows all data (Total 2025 Vs Total 2024)

What I expect when Filtering Store_LFL_Status "Yes":
1. Exclude all stores that has no sales in both years same DAY (Apple to Apple) and this should also reflects on YTD & LYTD

What I expect to have when filtiring Store_LFL_Status "No":
1. Sales CY return stores that have sales in 2025 but no sales in 2024 same day
2. LY return either blank either stores that have sales in 2024 but not any more in 2025 same day
3. Accurate reflection if same data on YTD & LYTD

I uploaded data sample and PBIX file here as the sample is not fitting here:
https://drive.google.com/drive/folders/1JwhwPKMbFoe_bPb-leaWPZTGDm6deJ6T?usp=drive_link

Thank you in advance!

[v-ssriganesh · Accepted solution]

Hello @Ann0,

I’ve reproduced your scenario and confirmed that it meets all the requirements you outlined:

• When filtering LFL_Status = Yes, the report shows only weekdays with sales in both years.
• The YoY and YTD measures calculate correctly.
• The output reflects the expected comparison on the same weekdays year over year.

For your reference, I am attaching the .pbix file, based on your dataset, every weekday has sales recorded in both 2024 and 2025. That’s why all weekdays are marked as “Like-for-Like = Yes.” When you filter the report by LFL_Status = Yes, no data is excluded because there are no weekdays with sales in only one year.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[v-ssriganesh]

Hello @Ann0,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-ssriganesh]

Hello @Ann0,

Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.
Please don’t forget to “Accept as Solution” and Give “Kudos” if the response was helpful.
Thank you.

[Greg_Deckler]

@Ann0 Any chance you can post sample data or, even better, a sample PBIX file? Very difficult to figure out what your semantic model actually looks like.

[Ann0]

Hi @Greg_Deckler, here is the file with the data i am using. Thank you
https://we.tl/t-ZQBXHmYPiE

[v-ssriganesh]

Hello @Ann0,
Thanks for sharing your Power BI template file (.pbit).

When attempting to open it, I encountered the following error:

C:\Users\SWY Takhasusi\Desktop\PBI\Sales Data (All Sub Franchies) 2024-2025.xls

This means the .pbit file is trying to connect to a local Excel file that isn’t available on our systems. Since template files don’t include data, Power BI attempts to load the original source, which leads to a refresh failure.

To help us investigate and reproduce your issue, please provide:

• A sample data file (Excel or CSV) that matches the structure you're using.
• A clear explanation of the expected output based on that sample.
• Please avoid screenshots of data and remove any sensitive or unrelated content.

Need help preparing your sample? Refer to this guide: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Looking forward to your updated file.

[Ann0]

Hello @v-ssriganesh !

Thank you for the headsup, I am new to this.
I edited the post with a new Google drive link to the data sample and .PBIX file

PBI file - Google Drive

[v-ssriganesh]

Hello @Ann0,
The link you shared seems to have incorrect data. Could you review it and provide the correct information, as I mentioned earlier regarding how to send the data?

[Ann0]

Hi @v-ssriganesh , 
Sorry for late response.

I updated the link, really dont know what happened I was sure I copied the correct one.

https://drive.google.com/drive/folders/1JwhwPKMbFoe_bPb-leaWPZTGDm6deJ6T?usp=drive_link

Thank you!

[v-ssriganesh · Accepted solution]

Hello @Ann0,

I’ve reproduced your scenario and confirmed that it meets all the requirements you outlined:

• When filtering LFL_Status = Yes, the report shows only weekdays with sales in both years.
• The YoY and YTD measures calculate correctly.
• The output reflects the expected comparison on the same weekdays year over year.

For your reference, I am attaching the .pbix file, based on your dataset, every weekday has sales recorded in both 2024 and 2025. That’s why all weekdays are marked as “Like-for-Like = Yes.” When you filter the report by LFL_Status = Yes, no data is excluded because there are no weekdays with sales in only one year.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[Ann0]

Thank you @v-ssriganesh  for sharing this.

Just two things:
1. The LY calculated column returning all blanks, not sure if something wrong as there was error message upon opening the file (version update)
2. You are absoultly right every weekday has data in both years as there are many stores, so if for example X store has data on both years same weekday and all Y store has only in 2025, still the measure will return both as we did not consider the store column in the measure.

However I have the latest version it shows this when opening the file.

LY calculated column returns all blanks.

[v-ssriganesh · Accepted solution]

Hello @Ann0,
Thank you for your feedback and for clarifying further.

Regarding your points:

The LY logic is implemented as a measure rather than a calculated column because dynamic time intelligence functions like SAMEPERIODLASTYEAR() only work in measures, not columns. The blank values you see are expected if you were checking a calculated column version.

Additionally, the error message you received when opening the file indicates that it was created with a newer version of Power BI Desktop. To ensure all measures work correctly, please install the latest version here: Download Microsoft Power BI Desktop from Official Microsoft Download Center

You’re correct currently, the LFL_Status is based only on whether each weekday has sales in both years overall. If you want to filter stores that had sales in both years on the same weekday (i.e., true Like-for-Like per store), you will need to modify the logic to include the Store# column in the calculation.

Specifically, you can create a Store_Weekday_Status table that summarizes sales per store and weekday and identifies whether that store had sales in both years.

I hope this clarifies everything. If it does, please consider marking this Accept as solution and giving it a Kudos to help others find it more easily.

Thank you.

[v-ssriganesh]

Hello @Ann0,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[Greg_Deckler]

@Ann0 Going to take some time to dig into this. That said, I expect that the issue will ultimately come down to your use of DAX's time intelligence functions coupled with CALCULATE. You might consider not using either of those and it may may your troubleshooting a lot easier.