Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Lookup within Date Range

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/...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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]]]
                )
              )
            )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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