Forum Discussion

TotalAnonymous's avatar
TotalAnonymous
Helper 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: Solv...
  • 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

v-achippa's avatar
v-achippa
Community 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
Super 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
    Helper III
    1 year ago

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