Value at the end of previous year

[JPO_10]

Hi all,

I need to calculate the average of value and value at the end of previous year. I can use measure for value and also value at the end of 2021 is solved. What I'm missing is end-of-year values for the year 2020.

I can't figure out why it works for 2022 (closing value of 2021) but not for 2021 (closing value of 2020). 

I'm using following the calculate Prev Year Close

Prev Year Close=

VAR acc_id = "SUM210P"

VAR _value =CALCULATE(ACT_CUM[ACT_CUM],

            ACCOUNTS[ACCKEYLAN_ACCKEYUDID]= acc_id,

            DATEADD(ENDOFYEAR('DATES'[Date]),-1,YEAR)

            )    

return

_value

Picture below describers the current status. During 2021 it should show 811 (value at Dec 2020). During 2022 it shows 818 as expected.

Cheers,

Juha

[amitchandak · Accepted solution]

@JPO_10 , You can use openingbalanceyear - https://youtu.be/6lzYOXI5wfo

it gives the last date of the last year

or

Last year last day sales  = CALCULATE(lastnonblankvalue(sales[Sales Date], SUM(Sales[Sales Amount])),previousyear('Date'[Date]))

[v-cgao-msft · Accepted solution]

Hi @JPO_10 ,

Please check the measures in PBIX file:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak · Accepted solution]

@JPO_10 , You can use openingbalanceyear - https://youtu.be/6lzYOXI5wfo

it gives the last date of the last year

or

Last year last day sales  = CALCULATE(lastnonblankvalue(sales[Sales Date], SUM(Sales[Sales Amount])),previousyear('Date'[Date]))