Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JPO_10
Frequent Visitor

Measure's value at the end of previous year

Hi all,

 

With the help from @amitchandak  and @v-cgao-msft  I managed to get previous year's closing values from column. Thank you, guys.

 

Now the problem is to get last year's closing value from measure. Measure itself work perfectly. Also SAMEPERIODLASTYEAR looks good. ROCE is calculated as follows:

ROCE =
VAR Profit = [Income, ROCE]
VAR _Month = DATES[Month, measure]
VAR _Inv = ([Investment opening] + [Investment]) / 2
Return
((Profit / _Month) *12) / _Inv
 
For ROCE_LY_EOY I have tried 
ROCE_LY_EOY = CALCULATE(
                ROCE[ROCE],
                DATEADD(ENDOFYEAR(DATES[Date]),-1,YEAR)
)
 
Instead of zeros in ROCE_LY_EOY, it should be 62,5% for this year.
 
JPO_10_0-1664981845193.png

 

Hope you can help.

 

Cheers,

J

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

you could try

ROCE_LY_EOY =
VAR CurrentYear =
    YEAR ( MAX ( 'Dates'[Date] ) )
VAR LastYear = CurrentYear - 1
RETURN
    CALCULATE (
        ROCE[ROCE],
        REMOVEFILTERS ( 'Dates' ),
        TREATAS ( { ( LastYear, "December" ) }, 'Dates'[Year], 'Dates'[Month name] )
    )

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

you could try

ROCE_LY_EOY =
VAR CurrentYear =
    YEAR ( MAX ( 'Dates'[Date] ) )
VAR LastYear = CurrentYear - 1
RETURN
    CALCULATE (
        ROCE[ROCE],
        REMOVEFILTERS ( 'Dates' ),
        TREATAS ( { ( LastYear, "December" ) }, 'Dates'[Year], 'Dates'[Month name] )
    )

Thank you @johnt75 for helping out!

Hi @johnt75 ,

 

your solution solved my problem!

 

Many Thanks,

 

J

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.