Forum Discussion

zombieug's avatar
zombieug
Icon for Helper I rankHelper I
8 years ago
Solved

Check if Date is <= another Date in Table

Hello Forum,

 

I have the following table:

 

Based on Material Key and SD-SA Key there are Delivery Dates and Release Dates.

What I want to create a measure or calculated column that checks for:

There is a Release Date "X" in the table that is > Release Date (Currently Assigned in the Row).

If there is than the Release Date "X" should be <= Delivery Date (Currently Assigned in the Row).

The example shown above should clarify, if you need more information let me know...

 

 

 

 

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    Hi@ zombieug 

    After my research , you can use EARLIER Function to add a column as below:

    check = IF(CALCULATE(MAX(Table4[Release Date]),FILTER(Table4,Table4[Release Date]<=EARLIER(Table4[Delivery Date])))=Table4[Release Date],1,0)

    Result:

     

    Best Regards,

    Lin

  • zombieug's avatar
    zombieug
    8 years ago

    Hi v-lili6-msft Ashish_Mathur,

     

    had to chance it a little to:

    IF(CALCULATE(MAX(Sheet1[Release Date]);FILTER(ALLEXCEPT(Sheet1;Sheet1[SD-SA Key];Sheet1[Material Key]);Sheet1[Release Date]<=EARLIER(Sheet1[Delivery Date])))=Sheet1[Release Date];1;0)

     

    this way the column will work for sd-sa key and material key

    I think it should work know.

    Thanks to all of you who tried to help.

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi@ zombieug 

    After my research , you can use EARLIER Function to add a column as below:

    check = IF(CALCULATE(MAX(Table4[Release Date]),FILTER(Table4,Table4[Release Date]<=EARLIER(Table4[Delivery Date])))=Table4[Release Date],1,0)

    Result:

     

    Best Regards,

    Lin

    • zombieug's avatar
      zombieug
      Icon for Helper I rankHelper I
      8 years ago

      Hi v-lili6-msft Ashish_Mathur,

       

      had to chance it a little to:

      IF(CALCULATE(MAX(Sheet1[Release Date]);FILTER(ALLEXCEPT(Sheet1;Sheet1[SD-SA Key];Sheet1[Material Key]);Sheet1[Release Date]<=EARLIER(Sheet1[Delivery Date])))=Sheet1[Release Date];1;0)

       

      this way the column will work for sd-sa key and material key

      I think it should work know.

      Thanks to all of you who tried to help.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Paste the data here such that i can copy it in an Excel workbook.

    • zombieug's avatar
      zombieug
      Icon for Helper I rankHelper I
      8 years ago

      Hi Ashish_Mathur thank you for trying to help.

      Delivery Date

      		Release DateMaterial KeySD-SA KeyQuantity
      02.02.201812.01.201800/02652906381VS00/242002362250
      02.02.201819.01.201800/02652906381VS00/242002362250
      02.02.201826.01.201800/02652906381VS00/242002362250
      02.02.201802.02.201800/02652906381VS00/242002362250
      04.02.201812.01.201800/02652906381VS00/242002362250
      04.02.201819.01.201800/02652906381VS00/242002362250
      04.02.201826.01.201800/02652906381VS00/242002362250
      04.02.201802.02.201800/02652906381VS00/242002362250
      06.02.201812.01.201800/02652906381VS00/242002364950
      06.02.201819.01.201800/02652906381VS00/242002364950
      06.02.201826.01.201800/02652906381VS00/242002364950
      06.02.201829.01.201800/02652906381VS00/242002364950
      06.02.201802.02.201800/02652906381VS00/242002364950
      06.02.201803.02.201800/02652906381VS00/242002364950
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        In the image that you have pasted in your first post, why is there a 0 in the first 3 rows.  Shouldn't there be a 1 in those rows and may be in others as well.  Could you kinldy check the entries in column F.