Need Urgent Help on SamePeriodLastYear

[Lio123]

Dear Pros, 

Can you please help me to calculate the below?

I have the sales data of 2024 & 2025 in the same table.
     1. I want to calculate the difference between 2025 month on month data and 2024 month on month.
     2. I want to calcuate the % differece between 2025 month on month data and 2024 month on month.
I also have the categories and subcategories, I want difference based on these level as well.

I want to show the result in a clustered column char

Thanks

[v-ssriganesh · Accepted solution]

Hi @Lio123,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I’ve reproduced your scenario in Power BI Desktop using a Date dimension and same-month comparison logic.
The month-on-month (same month last year) difference and % difference are working as expected at Month, Category, and SubCategory levels.

Based on the reproduction, the output matches the requirement you described (e.g: Jan-2025 vs Jan-2024, Feb-2025 vs Feb-2024). For your reference, I’m attaching the .pbix file so you can review the model, measures and visuals used.

Best regards,
Ganesh Singamshetty.

[Jaywant-Thorat]

Hi  No worries — this is a very common SAMEPERIODLASTYEAR / YoY confusion, and I’ll fix it step-by-step, clean and production-ready.

You do NOT need two tables. One table is perfectly fine 👍

---

✅ Prerequisite (VERY IMPORTANT)

1️⃣ You MUST have a proper Date table

Create a Date table (if not already):

Date = ADDCOLUMNS ( CALENDAR (DATE(2024,1,1), DATE(2025,12,31)), "Year", YEAR([Date]), "Month", FORMAT([Date], "MMM"), "MonthNo", MONTH([Date]), "YearMonth", FORMAT([Date], "YYYY-MMM") )

➡️ Mark it as Date Table
➡️ Create relationship:

Date[Date] → Sales[Date]

⚠️ SAMEPERIODLASTYEAR will not work correctly without this

---

✅ Base Measure (Mandatory)

Total Sales = SUM ( Sales[Amount] )

This automatically respects:

• Year

• Month

• Category

• SubCategory

1️⃣ Last Year Sales (Month-on-Month)

Sales LY := CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Date[Date] ) )

✔ This fetches same month last year
✔ Works at Category & SubCategory level automatically

---

2️⃣ Difference (2025 vs 2024 – Month on Month)

Sales Difference = [Total Sales] - [Sales LY]

Example:

• Jan 2025 Benz = 150

• Jan 2024 Benz = 100

• Difference = 50

3️⃣ % Difference (YoY %)

Sales % Difference := DIVIDE ( [Sales Difference], [Sales LY], 0 )

Format as Percentage

Example: (150 - 100) / 100 = 50%

✅ Works Automatically For:

✔ Year
✔ Month
✔ Category
✔ SubCategory

No extra DAX needed 🎯
Filter context does everything.

---

📊 Clustered Column Chart Setup

Axis = Date[Month]

Legend = Year

Values = Total Sales Sales Difference (optional)

OR

Tooltip = Sales LY, Sales Difference, Sales % Difference

🚫 Common Mistakes (Please Check)

❌ Using Year / Month from Sales table
❌ No Date table
❌ Using TEXT month (Jan, Feb) without MonthNo sort
❌ Using SAMEPERIODLASTYEAR on non-date column

🧠 Fine Note:

• SAMEPERIODLASTYEAR does not compare rows
• It shifts filter context back by 1 year

  That’s why Date table is non-negotiable.

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat:  https://tinyurl.com/JoinMissionPowerBIBharat
#MissionPowerBIBharat
LIVE with Jaywant Thorat 

[Praful_Potphode · Accepted solution]

Hi @Lio123 ,

Assuming you have a Fact Table connected to a DIm table, you can use attached pbix as reference for ALL YOY,MOM,QOQ calculations.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[v-ssriganesh]

Hello @Lio123,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Lio123,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @Lio123,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I’ve reproduced your scenario in Power BI Desktop using a Date dimension and same-month comparison logic.
The month-on-month (same month last year) difference and % difference are working as expected at Month, Category, and SubCategory levels.

Based on the reproduction, the output matches the requirement you described (e.g: Jan-2025 vs Jan-2024, Feb-2025 vs Feb-2024). For your reference, I’m attaching the .pbix file so you can review the model, measures and visuals used.

Best regards,
Ganesh Singamshetty.

[cengizhanarslan]

Assuming your amount column is Fact[Amount] and you have a Date table 'Date':

Sales =
SUM ( Fact[Amount] )

Sales LY =
CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Diff vs LY =
[Sales] - [Sales LY]

% Diff vs LY =
DIVIDE ( [Diff vs LY], [Sales LY] )

[Lio123]

Hi @cengizhanarslan ,

Thanks for your response!

However, it is not giving the correct result.

Attaching the data and result of the dax

[cengizhanarslan]

Do you have date dimention table and marked it as calendar table?

[Lio123]

@cengizhanarslanyes I have a date dim table.

[FreemanZ]

Hi @Lio123 ,

Supposing you have both year and month name in sales table as well, try the following:
Sales LY = CALCULATE([Sales], sales[year]=MAX(sales[year])-1)

or tell us more about your situation?

[Lio123]

@FreemanZ  thanks for your response, but I want month on month difference not just year