Forum Discussion

TotalAnonymous's avatar
TotalAnonymous
Icon for Helper III rankHelper III
1 year ago
Solved

Follow up on Previous Year Measure Issue

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




  • v-achippa's avatar
    v-achippa
    1 year ago

    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

8 Replies

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    1 year ago

    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

    • FBergamaschi's avatar
      FBergamaschi
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
      TotalAnonymous
      Icon for Helper III rankHelper III
      1 year ago

      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's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        1 year ago

        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

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      TotalAnonymous
      Icon for Helper III rankHelper III
      1 year ago

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