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

 

DateHistReserveHist
02/05/2023505
03/05/202311005
15/05/20236505
02/06/20238007.2
14/06/20238207.2
26/06/20238092.2
28/06/20237867.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

DateHow to Create This Column?
17/05/20236505
18/05/20236505
02/06/20238007.2
14/06/20238207.2
26/06/20238092.2
28/06/20237867.2
27/07/20237867.2
22/08/20237867.2


does anyone have any ideas? 

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

3 Replies

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