Forum Discussion
Create a target column based on previous year values
Dear all,
I would like to create a column in my table where I have to give the value of EBITDA of last year to the current year as a target.
I tried the following DAX formula but it is not working.
EBITDA target = LOOKUPVALUE(Table[EBITDA], Table[StartDate], SAMEPERIODLASTYEAR(Table[StartDate]))
It is giving me the following message : A table of multiple values was supplied where a single value was expected.
I also tried to create a unique key of current year and lastyear with a concatenation of (Company, Segment, SubSegment and StartDate columns) and I still got the same error message.
Below is the output that I need to create.
Many thanks for your help in advance.
Anonymous , A new colum
new column =
var _com = [company]
var _start = eomonth([start_date],-12)
return
sumx(filter(Table, [company] =_com && [start_date] =_start), [EBITA])
4 Replies
I also tried to create a rank column based on StartDate :
Period Rank = RANKX(all('Database'),Database[StartDate],,ASC,Dense)and Create a measure :EBITDA Target = CALCULATE( [EBITDA Measure], FILTER(ALL('Database'),'Database'[Period Rank]=max('Database'[Period Rank])-1))and this is giving me the sum of EBITDA that is the same for every row.Can anybody help please?Anonymous , A new colum
new column =
var _com = [company]
var _start = eomonth([start_date],-12)
return
sumx(filter(Table, [company] =_com && [start_date] =_start), [EBITA])
amitchandak : I just tried the code and it is giving me empty values.
Any idea where the issue comes from? Thanks a lot for your help again
amitchandak : the issue was in the var of Date, I needed to have the start of the month from last year instead of end of the month.
STARTOFMONTH ( DATEADD ( Table[StateDate], -12, MONTH ) )Thanks a lot