Filtering same column for two conditions

[wjjunyor]

Having issue to complete the following: 

We do have a EndTermDate column and need to show those which end term is until the end of this month and also those in wich there is no EndTermDate. 

We are stuck in here: 

ORDERBY(
  (
    SELECT(
      Contract[ContractID], 
      EOMONTH([ContractEndTerm], 0)
      > 
      TODAY()
    )
  ), 
  [ContractLabel]
)

[WillowMobileSys]

You can use the OR() and AND() functions.  Also check out the  MONTH() function.

---

@wjjunyor wrote:

need to show those which end term is until the end of this month and also those in wich there is no EndTermDate.

---

I'm not clear what is meant by "until the end of this month".  I assume you mean any contracts that end THIS month.  If true, your updated expression should be something like this:

ORDERBY(
        SELECT(
               Contract[ContractID],
               OR(
                   ISBLANK([ContractEndTerm]),
                   MONTH([ContractEndTerm]) = MONTH(TODAY())
               )
        ),
        [ContractLabel]
)

I hope this helps!

[Joseph_Seddik]

ORDERBY(
  FILTER("Contract", 
    OR(
      ISBLANK([contractEndTerm]), 
      AND(
        MONTH(TODAY()) = MONTH(EOMONTH([contractEndTerm], 0)), 
        [contractEndTerm] > TODAY()
      )
    )
  ), [contractTable]
)

[WillowMobileSys]

There is no need to do MONTH(EOMONTH(...) ).  Only MONTH() is needed.  They both will return the SAME month value.

Yes,  "[contractEndTerm] > TODAY()", would be needed IF the intention was to surface ONLY those contracts that end between NOW to the end of the month.

[Joseph_Seddik]

---

@WillowMobileSys wrote:

There is no need to do MONTH(EOMONTH(...) ).  Only MONTH() is needed.  They both will return the SAME month value.

---

Actually it is needed, otherwise you'd need to also add YEAR(...) = YEAR(...)

---

@WillowMobileSys wrote:

IF the intention was to surface ONLY those contracts that end between NOW to the end of the month

---

This is what I understood the aim was. 

BTW, sorry haven't noticed your first post when I wrote mine; just seen it. 

[WillowMobileSys]

---

@Joseph_Seddik wrote:

Actually it is needed, otherwise you'd need to also add YEAR(...) = YEAR(...)

---

Now that you have pointed it out, I think a check by YEAR needs to be included no matter what.

Maybe I'm missing something on the EOMONTH()?? Here's some examples:

End Date = 5/13/2024 ==> EOMONTH( ...,0) = 5/31/2024 ==> MONTH = 5

End Date = 5/13/2025 ==> EOMONTH(..., 0) = 5/31/2025 ==> MONTH = 5

Whether you use MONTH or MONTH(EOMONTH()) on the same date...you will always get the same month value.  right?

what am I missing?

[wjjunyor]

Long time no see - Sorry for taking this long to return to this question.  Indeed I use EOMONTH, because this expression is used as conditional validation and allow users to pick active contracts or those with ending month in the current month. 

[WillowMobileSys]

If you found a way that works that's great.  Be sure that Contracts of DIFFERENT Years work correctly.  I think you may need to add to the condition a check on YEAR as well.