cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculating most recent previous value from another column

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.
 
SiteDateActual ValueMost recent actual value
Site A1-Jun1010
Site A2-Jun 10
Site A3-Jun 10
Site A4-Jun 10
Site A5-Jun 10
Site A6-Jun200200
Site A7-Jun 200
Site A8-Jun 200
Site A9-Jun 200
Site A10-Jun 200
Site B1-Jun3030
Site B2-Jun 30
Site B3-Jun 30
Site B4-Jun100100
Site B5-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!
1 ACCEPTED SOLUTION
Community Support
Community Support

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]))

create measure.JPG

Best Regards

Rena

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

View solution in original post

2 REPLIES 2
Community Support
Community Support

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]))

create measure.JPG

Best Regards

Rena

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

View solution in original post

Super User II
Super User II

@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)

jdbuchanan71_0-1594326799105.png

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors