Now is 2020 How to get 2021 Target amount ?

[Anonymous]

Hi All

I have 2020 sales target amount in PBI , i can view 2020 target amount using below expression :-

Target YTD =

var _max = today()

return

calculate(TOTALYTD(('TARGET_'[TARGET_]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

or

Target_N_1 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],1,YEAR)))

I have add 2021 sales target amount to PBI , may i know how to modify the below expression , in order to display 2021 Target amount :-

Target_N_1 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],1,YEAR)))

Paul

[AllisonKennedy]

@Anonymous 

Update the YTD measure: 

Target YTD =

var _max = MAX(Date[Date])

return

calculate(TOTALYTD(('TARGET_'[TARGET_]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

And add a filter or slicer for Date[Date] to your report, select year 2021 in the slicer, the target should update.

[amitchandak]

@Anonymous , Id this formula not working

Target_N_1 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],1,YEAR)))

This one should work

[Anonymous]

Hi Amit

Below are the expression work fine :-

For 2019 Target amount :-

Target_2019 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-3,YEAR)))

For 2020 Target amount :-

Target_2020 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-2,YEAR)))

For 2021 Target amount :-

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))

May question is why 2020 current year , the expression using -2 ? why cannot using 0 , since it current year.

Paul 

[AllisonKennedy]

@Anonymous  What is the last date in your Target table and last date in your Date table? This will impact what number (0, -1, -2, -3, etc) that you must use in the DATEADD function.

[Anonymous]

Hi Allison

In case now is 2020 Dec and i just add 2021 Target , what is the Date i should enter ? So that i can use 1 for 2021 as Target ?

Paul

[AllisonKennedy]

Sorry @Anonymous  I don't understand your question. Where are you wanting to enter the date?

[Anonymous]

Hi Allison

I indicate the end date 2022. ( see my image below ) and my 2021 Target expression =

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))

[amitchandak · Accepted solution]

@Anonymous , for 2021 this one should work, make sure date table has dates for 2021

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],1,YEAR)))