topic Re: Follow up on Previous Year Measure Issue in DAX Commands and Tips

Follow up on Previous Year Measure Issue

TotalAnonymous — Wed, 11 Jun 2025 03:36:13 GMT

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=108731847895513632157&rtpof=true&sd=true

Re: Follow up on Previous Year Measure Issue

v-achippa — Wed, 11 Jun 2025 11:38:40 GMT

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

Re: Follow up on Previous Year Measure Issue

Rupak_bi — Wed, 11 Jun 2025 11:56:50 GMT

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 )

Re: Follow up on Previous Year Measure Issue

FBergamaschi — Wed, 11 Jun 2025 13:52:08 GMT

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

Re: Follow up on Previous Year Measure Issue

TotalAnonymous — Thu, 12 Jun 2025 00:14:53 GMT

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

Re: Follow up on Previous Year Measure Issue

TotalAnonymous — Thu, 12 Jun 2025 00:15:52 GMT

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

Re: Follow up on Previous Year Measure Issue

TotalAnonymous — Thu, 12 Jun 2025 00:16:55 GMT

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

Re: Follow up on Previous Year Measure Issue

v-achippa — Fri, 13 Jun 2025 12:30:11 GMT

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

Re: Follow up on Previous Year Measure Issue

v-achippa — Mon, 16 Jun 2025 13:05:47 GMT

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