Need Urgent Help on SamePeriodLastYear

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

DAX

Need Urgent Help

Sameperiodlastyear

v-ssriganesh
9 months ago
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.
4922389.pbix46 KB
Praful_Potphode
8 months ago
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

TimeIntelligence_Demo.pbix83 KB

11 Replies

Praful_Potphode
Super User
8 months ago
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

TimeIntelligence_Demo.pbix83 KB
cengizhanarslan
Super User
9 months ago
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
  Helper II
  9 months ago
  Hi cengizhanarslan ,
  
  Thanks for your response!
  
  However, it is not giving the correct result.
  
  Attaching the data and result of the dax
  - FreemanZ
    Super User
    9 months ago
    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?
v-ssriganesh
Community Support
9 months ago
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.
4922389.pbix46 KB
v-ssriganesh
Community Support
9 months ago
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
Community Support
9 months ago
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.
Jaywant-Thorat
Super User
8 months ago
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