Check if Date is <= another Date in Table

[zombieug]

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...

[v-lili6-msft · Accepted solution]

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

[v-lili6-msft · Accepted solution]

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 · Accepted solution]

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]

Hi,

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

[zombieug]

Hi @Ashish_Mathur thank you for trying to help.

Delivery Date	Release Date	Material Key	SD-SA Key	Quantity
02.02.2018	12.01.2018	00/02652906381VS	00/24200236	2250
02.02.2018	19.01.2018	00/02652906381VS	00/24200236	2250
02.02.2018	26.01.2018	00/02652906381VS	00/24200236	2250
02.02.2018	02.02.2018	00/02652906381VS	00/24200236	2250
04.02.2018	12.01.2018	00/02652906381VS	00/24200236	2250
04.02.2018	19.01.2018	00/02652906381VS	00/24200236	2250
04.02.2018	26.01.2018	00/02652906381VS	00/24200236	2250
04.02.2018	02.02.2018	00/02652906381VS	00/24200236	2250
06.02.2018	12.01.2018	00/02652906381VS	00/24200236	4950
06.02.2018	19.01.2018	00/02652906381VS	00/24200236	4950
06.02.2018	26.01.2018	00/02652906381VS	00/24200236	4950
06.02.2018	29.01.2018	00/02652906381VS	00/24200236	4950
06.02.2018	02.02.2018	00/02652906381VS	00/24200236	4950
06.02.2018	03.02.2018	00/02652906381VS	00/24200236	4950

[Ashish_Mathur]

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.