SAMEPERIODLASTYEAR gives incorrect month for Last Year Value

[cj_oat]

Hi guys,

I'm trying to use SAMEPERIODLASTYEAR for MTD data, however, I found that TY month and LY month are different month (here I adjusted the formula to see what is the month that formula refering to). As you see in the screenshot for TY MTD Month is showing 2025001 which is the latest month for calendar year (the year start counting from December as 1st month) while for LY MTD Month is showing 2024012 which was only 1 month different from TY MTD Month and they are different month.

Not sure how should I correct the formula to see LY MTD Month as 2024001 (which is one year different from TY MTD Month)

TY Formula:

__TY MTD (Net Sales Value) = 
VAR LatestMonthInTable = CALCULATE(max('Sales Report'[Month]),'Sales Report'[Year]=MAX('Sales Report'[Year]),REMOVEFILTERS('Sales Report'))

VAR SelectedMonth=
    CALCULATE(MAX('Calendar Table (Date)'[Month]),'Calendar Table (Date)'[Month]<=LatestMonthinTable)

RETURN
SelectedMonth

LY (SAMEPERIODLASTYEAR) Formula:

__LY MTD Month = 
CALCULATE([__TY MTD (Net Sales Value)],SAMEPERIODLASTYEAR('Calendar Table (Date)'[Date]),'Sales Report'[Week Number]<=MAX('Sales Report'[Week Number]))

[v-yohua-msft]

Hi, @cj_oat 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[powerbiexpert22]

Hi @cj_oat ,

Please follow below , please see below power bi file as reference

https://drive.google.com/file/d/1qQQ3p4KturyUUEBcWukqv6yIH6DdeXxu/view?usp=drive_link

1. Create calculated column 

Year = Mid(FORMAT('Sales Report'[Year - Month],"#####"),1,4)

2. Create another calculated column 

Date = DATE('Sales Report'[Year],'Sales Report'[Month Number],1)

3. Modify your measure as below 

__LY MTD (Month that formula refer to) =

FORMAT(EDATE(max('Sales Report'[Date]),-12),"YYYY0MM")

[cj_oat]

Hi @powerbiexpert22 ,

Here I have provided link to share example of data, so basically I want to compare data between this year and last year for MTD but as screenshot below, TY value is showing 2025001 but LY turned out to show LY in 2024012 instead of 2024001...I'm not sure how SAMEPERIODLASTYEAR actually works

Google Drive - Example Data

[powerbiexpert22]

Hi @cj_oat , requested access for dataset

[cj_oat]

Hi @powerbiexpert22 ,

Just updated the access, can you try again if you can access/download the file?

[powerbiexpert22]

Hi @cj_oat ,

please share sample data and expected results