topic Re: Need Urgent Help on SamePeriodLastYear in DAX Commands and Tips

Need Urgent Help on SamePeriodLastYear

Lio123 — Wed, 21 Jan 2026 08:26:07 GMT

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

Re: Need Urgent Help on SamePeriodLastYear

cengizhanarslan — Wed, 21 Jan 2026 08:44:33 GMT

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] )

Re: Need Urgent Help on SamePeriodLastYear

Lio123 — Wed, 21 Jan 2026 09:04:42 GMT

Hi cengizhanarslan ,

Thanks for your response!

However, it is not giving the correct result.

Attaching the data and result of the dax

Re: Need Urgent Help on SamePeriodLastYear

FreemanZ — Wed, 21 Jan 2026 09:21:09 GMT

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?

Re: Need Urgent Help on SamePeriodLastYear

cengizhanarslan — Wed, 21 Jan 2026 10:13:04 GMT

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

Re: Need Urgent Help on SamePeriodLastYear

Lio123 — Wed, 21 Jan 2026 10:49:43 GMT

cengizhanarslanyes I have a date dim table.

Re: Need Urgent Help on SamePeriodLastYear

Lio123 — Wed, 21 Jan 2026 10:50:33 GMT

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

Re: Need Urgent Help on SamePeriodLastYear

v-ssriganesh — Thu, 22 Jan 2026 11:23:38 GMT

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.

Re: Need Urgent Help on SamePeriodLastYear

v-ssriganesh — Tue, 27 Jan 2026 04:39:47 GMT

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.

Re: Need Urgent Help on SamePeriodLastYear

v-ssriganesh — Fri, 30 Jan 2026 10:38:36 GMT

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.

Re: Need Urgent Help on SamePeriodLastYear

Praful_Potphode — Sat, 07 Feb 2026 12:22:24 GMT

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

Re: Need Urgent Help on SamePeriodLastYear

Jaywant-Thorat — Sat, 07 Feb 2026 20:26:27 GMT

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)

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