topic DAX Lookup within Date Range in DAX Commands and Tips

DAX Lookup within Date Range

Anonymous — Wed, 08 May 2024 07:46:50 GMT

hi, i have this Table1 that shows reservehist changes occured on each datehist

DateHist	ReserveHist
02/05/2023	505
03/05/2023	11005
15/05/2023	6505
02/06/2023	8007.2
14/06/2023	8207.2
26/06/2023	8092.2
28/06/2023	7867.2

how do i create the column on the right below? i need this column to read from the table above - for example, 17/05/2023 falls between 15/05 and 02/06, that means the value should be 6505.
the last change happened on 28/06, so any dates below after 28/06 should show 7867.2

Date	How to Create This Column?
17/05/2023	6505
18/05/2023	6505
02/06/2023	8007.2
14/06/2023	8207.2
26/06/2023	8092.2
28/06/2023	7867.2
27/07/2023	7867.2
22/08/2023	7867.2

does anyone have any ideas?

Re: DAX Lookup within Date Range

bhanu_gautam — Wed, 08 May 2024 10:11:56 GMT

Anonymous ,Try using below method

ReserveHistValue =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
CALCULATE(
LASTNONBLANK('Table1'[ReserveHist], 1),
FILTER(
ALL('Table1'),
'Table1'[DateHist] <= SelectedDate
)
)

Re: DAX Lookup within Date Range

Anonymous — Thu, 09 May 2024 00:16:48 GMT

thanks for your reply, but this didnt work sadly 😞 any suggestions why?

Re: DAX Lookup within Date Range

Anonymous — Thu, 09 May 2024 01:43:57 GMT

Actually i resolved it myself taking a direction from your query - thanks so much was very helpful.
for anyone interested, this was the solution that worked perfectly.
i jsut needed to add an additional ID column as well to the tables, but you can just remove those bits from this query below and use it.

ReserveHistValue =

CALCULATE(

LASTNONBLANK('Reserve History'[Section5 Removed], 1),

FILTER(

'Reserve History',

'Reserve History'[FactCaseReserveHistory[Caseref]]] = EARLIER('UNION'[CLAIM_REFERENCE_SUPPLIER]) &&

'Reserve History'[FactCaseReserveHistory[AuditTimeStamp]]]=

MAXX(

FILTER(

'Reserve History',

'Reserve History'[FactCaseReserveHistory[Caseref]]] = EARLIER('UNION'[CLAIM_REFERENCE_SUPPLIER]) &&

'Reserve History'[FactCaseReserveHistory[AuditTimeStamp]]] <= EARLIER('UNION'[BOOKING_DATE_SUPPLIER])),

'Reserve History'[FactCaseReserveHistory[AuditTimeStamp]]]

)