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]]]
                )
              )
            )
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Anonymous ,Try using below method

 



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

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