How to calculate impact of category

[Onlynearyou]

Hi, 

I'm trying to figure out how to do an impact analysis. For this I have a table of objects (real estate). Each property has a portfolio score and a financial score. These are calculated every year. When an intervention is planned (none, basic, middle) the portfolio score and financial score improve, when nothing is done they deteriorate. After all, the real estate wears out. I would like to figure out this impact. So that I can indicate that when intervention basic or middle or none is chosen, this can lead to a +X or -X for the portfolio score or +X or -X for the financial score.

is there a way to do this with DAX? 

Table name is HoldSell

[v-yalanwu-msft · Accepted solution]

Hi, @Onlynearyou ;

You could create a column by dax.

Column = 
var _max=CALCULATE(MAX('Table'[Date ]),FILTER('Table',[ID 1]=EARLIER('Table'[ID 1])&&[Date ]<EARLIER('Table'[Date ])))
var _last= CALCULATE(SUM([Portfolio Score]),FILTER(ALL('Table'),[ObjectNumber]=EARLIER('Table'[ObjectNumber])&&[Date ]=_max))
return IF([Portfolio Score]<= _last||_last=BLANK(),"None","Basic")

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.