Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
2 years ago
Solved

How do I get a value within a range

Hi,

 

  I have the following table (tblPIListing):

 

PI    Start Date             End Date

PI1  01/03/2024           03/26/2024

PI2  03/27/2024           07/02/2024

PI3  07/03/2024           09/24/2024

PI4  09/25/2024           12/31/2024

 

  I have another table(OnlyFeatureswithParentKey) and need to get 2 PIs, one based on the Start Date and the other on End Date:

 

Key        Start Date       End Date       PIStartAlignment   PIEndAlignment

123        01/03/2024    04/29/2024    PI1                        PI2

456       07/04/2024     09/24/2024    PI3                        PI3

 

Here Is my logic:

 

PIFeaturesFallsInBasedOnFeaturesDueDate =
 var matchingqtr = FILTER(tblPIListing,tblPIListing[EndDate]<= OnlyFeaturesWithParentKey[DueDate])
 var CountQtr = COUNTROWS(matchingqtr)
 return IF(CountQtr>=1,MAXX(matchingqtr,tblPIListing[PI]),"N/A")
 
PIFeaturesFallsInBasedOnFeaturesDueDate =
 var matchingqtr = FILTER(tblPIListing,tblPIListing[EndDate]<= OnlyFeaturesWithParentKey[DueDate])
 var CountQtr = COUNTROWS(matchingqtr)
 return IF(CountQtr>=1,MAXX(matchingqtr,tblPIListing[PI]),"N/A")
 
This doesn't seem to work as Key 123 is getting the FeaturesFallsInBasedOnFeaturesDueDate correct, but PIFeaturesFallsInBasedOnFeaturesDueDate is getting PI1 instead of PI2
Need Help
  • EaglesTony's avatar
    EaglesTony
    2 years ago

    Here is the solution that works for me:

     

    PIDateThatEndDateFallsIn =
     var matchingqtr = FILTER(tblPIListing,OnlyFeaturesWithParentKey[DueDate] >= tblPIListing[StartDate] && OnlyFeaturesWithParentKey[DueDate] <= tblPIListing[EndDate])
     var CountQtr = COUNTROWS(matchingqtr)
     return IF(CountQtr>=1,MAXX(matchingqtr,tblPIListing[EndDate]),BLANK())

6 Replies

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      2 years ago

      That looks like what I need.

       

      But not sure how that solves my issue to select PI2 instead of PI1 based on:

      PI1  01/03/2024           03/26/2024

      PI2  03/27/2024           07/02/2024

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi EaglesTony - can you please try the below logic using lookupvalue 

     

    PIStartAlignment =
    LOOKUPVALUE(
        PITest[PI],
        PITest[Start Date],
        CALCULATE(
            MAX(PITest[Start Date]),
            FILTER(
                PITest,
                PITest[Start Date] <= OnlyFeatureswithParentKey[Start Date] &&
                PITest[End Date] >= OnlyFeatureswithParentKey[Start Date]
            )
        )
    )
     
    PIEndAlignment1 =
    LOOKUPVALUE(
        PITest[PI],
        PITest[Start Date],
        CALCULATE(
            MAX(PITest[Start Date]),
            FILTER(
                PITest,
                PITest[Start Date] <= OnlyFeatureswithParentKey[ End Date] &&
                PITest[End Date] >= OnlyFeatureswithParentKey[ End Date]
            )
        )
    )
     
    Hope it works.

     

     

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      2 years ago

      It gave me blank, here is my logic:

       

      PIFeaturesFallsInBasedOnFeaturesDueDate =
      LOOKUPVALUE(
          tblPIListing[PI],
          tblPIListing[EndDate],
          CALCULATE(
              MAX(tblPIListing[StartDate]),
              FILTER(
                  tblPIListing,
                  tblPIListing[StartDate] <= OnlyFeaturesWithParentKey[DueDate] &&
                  tblPIListing[EndDate] >= OnlyFeaturesWithParentKey[DueDate]
              )
          )
      )
      • EaglesTony's avatar
        EaglesTony
        Post Prodigy
        2 years ago

        This is giving me blank value for the following:

         

        OnlyFeaturesWithParentKey[Start Date]="06/17/2024"

        OnlyFeaturesWithParentKey[DueDate] ="08/31/2024"

         

        It is giving me 7/2/2024 instead of 9/24/2024.

         
        Here is my logic:

        PIFeaturesFallsInBasedOnFeaturesDueDate =
        LOOKUPVALUE(
        tblPIListing[PI],
        tblPIListing[EndDate],
        CALCULATE(
        MAX(tblPIListing[EndDate]),
        FILTER(
        tblPIListing,
        OnlyFeaturesWithParentKey[DueDate] >= tblPIListing[StartDate] && OnlyFeaturesWithParentKey[DueDate] <= tblPIListing[EndDate]

        )
        )
        )

         

        I even tried this:, but it gives me an error "Expressions that yield variant data-type cannot be used to define calculated columns", but it seems to do what  I want, if I don't find anything it should return "N/A"

         

         var matchingqtr = FILTER(tblPIListing,OnlyFeaturesWithParentKey[DueDate] >= tblPIListing[StartDate] && OnlyFeaturesWithParentKey[DueDate] <= tblPIListing[EndDate])
         var CountQtr = COUNTROWS(matchingqtr)
         return IF(CountQtr>=1,MAXX(matchingqtr,tblPIListing[EndDate]),"N/A")