Forum Discussion
Helper VLast known value per year
Hello,
I need your help to calculate ( Last known value per year, - with a calculation that triggers on an entry date and ends with an exit date I give you data base model with result expected. I have atttached the link of the actual file as well
I have below data in table:
Second tale is below:
Expected output table is below:
Below is the file link of excel so that you can see the actual data:
Hi Junaid11 ,
Add a visual-level measure to filter,
Measure = VAR _DATE=MAX('VALORISATION'[VALUE_DATE]) RETURN IF(MONTH(_DATE)=12&&DAY(_DATE)=31,1)Set show items when the value is 1.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
Junaid11 , Not very clear
but you can try like
calculate(lastnonblankvalue(Table[Value Date], Sum(Table[Volarizaton_total]) )
I don't get it. The total for Asset 998 in 2017 is 1.375.000, but you want the value 1.400.000 (2018 value) to appear? Beause the SALE_DATE for asset 998 is 2018?
if so, why does asset 1000 not follow the same logic?
Asset 1000 have the values
2017 - 19.000.000 - But you want 18.900.000 to be show? Why? Why not 2019?
2018 - 18.900.000
2019 - 19.540.000
Asset 1011 has the current year values, except for 2021, which is blank and takes the previous year total?
nasolmeHelper I
This is an error for the asset number the good value is 19 000 000 €.
As I understand, asset 998 was bought in 31/12/2017 and he was sold on 31/12/2019. So he want's only get the asset value >=31/12/2017 and <= 2019.
Asset number 1011 don't have new value in 2021, so he want's take last known value (3 270 000 in 2020).
Finally, asset 1004 to 1011 haven't SALE_DATE, so we have to take last known value for 2022.- nasolmecurrent_value = CALCULATE(SUMX(FILTER(VALORISATION,VALORISATION[INTEGRATION_DATE]<=max(CALENDRIER[Date])&& (ISBLANK(VALORISATION[SALE_DATE]) || VALORISATION[SALE_DATE] >= max(CALENDRIER[Date]) ) ),(VALORISATION[Valorisation_total])))
With this measure we can get value per year. It works only if we have value every year.
Now, the solution is missing to retrieve the last known value if there is no date of sale
Hi Junaid11 ,
Add a visual-level measure to filter,
Measure = VAR _DATE=MAX('VALORISATION'[VALUE_DATE]) RETURN IF(MONTH(_DATE)=12&&DAY(_DATE)=31,1)Set show items when the value is 1.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
