Calculating most recent previous value from another column

[luke_f]

Hi everyone - I'm looking for guidance or examples about a calculation.

To simplify my scenario, let's say I have a building. This building can hold a certain number of items (a limit). That limit can change over time, and the value is provided irregularly. I also have daily observations about how many items are actually in that building. For each of those observations, I also want to show the most recently reported limit.

Site	Date	Actual Value	Most recent actual value
Site A	1-Jun	10	10
Site A	2-Jun		10
Site A	3-Jun		10
Site A	4-Jun		10
Site A	5-Jun		10
Site A	6-Jun	200	200
Site A	7-Jun		200
Site A	8-Jun		200
Site A	9-Jun		200
Site A	10-Jun		200
Site B	1-Jun	30	30
Site B	2-Jun		30
Site B	3-Jun		30
Site B	4-Jun	100	100
Site B	5-Jun		100

I can't quite figure this out in DAX... I think this needs to be a new calculated column instead of a measure in order to calculate a value for each date and site. LASTDATE does not work because it looks only at the latest value instead of the most recent one relative to each date.

Any suggestions or examples? Thank you for ideas!

[yingyinr · Accepted solution]

Hi @luke_f ,

You can create a measure as below:

Measure = 
var _curdate=MAX('Table'[Date])
var _predate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<_curdate&&NOT(ISBLANK('Table'[Actual Value]))))
var _prenum=CALCULATE(MAX('Table'[Actual Value]),FILTER(ALL('Table'),'Table'[Date]=_predate&&'Table'[Site]=MAX('Table'[Site])))
return if(ISBLANK(MAX('Table'[Actual Value])),_prenum,MAX('Table'[Actual Value]))

Best Regards

Rena

[jdbuchanan71]

@luke_f 

Give this a try.

Column = 
VAR _RowDate = 'Table'[Date]
RETURN
CALCULATE(LASTNONBLANK('Table'[Actual Value],TRUE()),ALLEXCEPT('Table','Table'[Site]),'Table'[Date] <= _RowDate)