Forum Discussion

suhank's avatar
suhank
New Member
3 years ago

last known value

Hello , 

I have columns in my table :

Date, Values

Every date has some values corresponding to it and some dates have no values. I would like to add additional column of values which shows value from last month . 

For eg : 

If I have jan 2022: 4

Feb 2022: 5

March 2022: 6 

 

the additional column should show me :

Jan 2022 : 5

Feb 2022: 6

March 2022:0

 

Any idea how can I achieve this ?

 

Below is another example :

 

 

Regards,

Ankit 

 

 

Need Help

2 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    3 years ago

    Hi!
    You can try below. It finds the date closest before to the current date, and then finds the Occupant Satisfaction of that record. Hope this helps!

    Last Known Value = 
VAR ThisDate = 'Table'[Date]
VAR PreviousDate = 
CALCULATE(
    max('Table'[Date]),
    FILTER(ALL('Table'), 'Table'[Date] < ThisDate
    )
)
return
CALCULATE(
    sum('Table'[Occupant satisfaction]),
    FILTER(ALL('Table'), 'Table'[Date] = PreviousDate
    )
)