Follow up on Previous Year Measure Issue

[TotalAnonymous]

Dear Community, 

I'd like to say thanks for always helping me solved my issue. I hope you're doing well. 

In this occassion, I'd like to follow up my thread that has been solved last time: Solved: Re: Previous Year measure - Function Dateadd expec... - Microsoft Fabric Community

Currently, I'm facing a struggle. 
As shown in the image below, The current YTD value of 2025 is up to week 202518. On the other hand, the current YTD value of Previous year is up to Week 202421 (In this case, is full month of May 2024)

Instead of returning YTD value up to 202421, I aim to return YTD value of Previous year up to 202418 (The value is shown in the 2024 - Expected Value table). The same week as the latest week in my fact data (202518). Would you mind give me an advice how to approach this issue, please? any advice, suggestion is really appreciated.

Thank you

Link to PBIX: https://drive.google.com/file/d/1m_bGwkZmnJARZhgdTjivY_duA7i-eTVs/view?usp=sharing
Link to Sample File: https://docs.google.com/spreadsheets/d/1_OT0buRDJWG5YW9LYwom_Or6vAWHsNgP/edit?usp=sharing&ouid=10873...

[Rupak_bi]

Hi @TotalAnonymous ,

Try below solution.

Offtake Sales - YTD PY = 
Var prev_year = year(max(calendar_new[Date]))-1
Var week_no = right(max('Sample Data'[WeekID]),2)
var week_id = value(prev_year&week_no)
var first_week_id = value(prev_year&0&1)
return
CALCULATE ( [Offtake Sales],all('Sample Data'),'Sample Promo Calendar'[WeekID]>=first_week_id,'Sample Promo Calendar'[WeekID]<=week_id )

[TotalAnonymous]

Hi @Rupak_bi , thanks for your suggestion. Please allow me to test it first and will come back to you ASAP

[v-achippa · Accepted solution]

Hi @TotalAnonymous,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for sharing the pbix file, based on your requirement please replace your Offtake Sales – YTD PY measure with this below measure

Offtake Sales – YTD PY =

VAR LatestWeekID_ThisYear =

    CALCULATE(

        MAX('Sample Data'[WeekID]),

        'calendar_new'[Year] = YEAR(TODAY())

    )

VAR TargetWeekNumber =

    MOD(LatestWeekID_ThisYear, 100)

VAR TargetYear = YEAR(TODAY()) - 1

VAR MinWeekID = TargetYear * 100 + 1

VAR MaxWeekID = TargetYear * 100 + TargetWeekNumber

RETURN

CALCULATE(

    SUM('Sample Data'[Sales Value]),

    FILTER(

        ALL('Sample Data'),

        'Sample Data'[WeekID] >= MinWeekID &&

        'Sample Data'[WeekID] <= MaxWeekID

    )

)

This will return YTD value of previous year up to 202418

Attached pbix file for your reference

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[TotalAnonymous]

Hi @v-achippa , thanks for your suggestion. Please allow me to test it first from my end and come back to you ASAP

[v-achippa]

Hi @TotalAnonymous,

As we haven’t heard back from you, we wanted to kindly follow up to check if you have got any chance to test? Is the issue resolved?
If my response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @TotalAnonymous,

We wanted to kindly follow up to check if you have got any chance to test? or Is the issue resolved?
If my response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[FBergamaschi]

My code is the following

Offtake Sales - YTD PY =

VAR MaxWeek = RIGHT ( CALCULATE( MAX ( 'Sample Data'[WeekID] ), REMOVEFILTERS() ), 2 )

VAR Year=SELECTEDVALUE( calendar_new[Year] )

VAR PY = Year-1

VAR MaxWeekPY = PY&MaxWeek

RETURN

CALCULATE (

    [Offtake Sales - YTD],

    'Sample Data'[WeekID]<=INT(MaxWeekPY),

    DATEADD ( calendar_new[Date], -1, YEAR )

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Francesco Bergamaschi

[TotalAnonymous]

Hi @FBergamaschi , thanks for your suggestion. Please allow me to test it first and will come back to you ASAP