Solved: Complex Date Expression

Ryan_Mortimer · 03-01-2021 03:51 PM

Hi everybody!

I have a field that I am trying to “automate” with an expression.

I have two columns, DATE IN and BILLING DATE.
Billing Date needs to compute based on the following:

Billing Date to be pre-filled from a value in another table, from column “SET BILLING DATE”
IF the DATE IN is after the value in SET BILLING DATE (from the other table), then it needs to be the value from DATE IN

Any help or steer in the right direction would be much appreciated! Thank you in advanced

Steve

Marc_Dillon

How do you intend to select the record from which to pull this SET BILLING DATE?

Ryan_Mortimer

I am planning to pull the data by using something like:

SELECT(StaticValues[Billing Date])

It is just a single row, single column entry (with a header of course) with the effective billing date.

It’s the IF statement that is confusing me. (I confess that I am not overly versed in Cross Table expressions, but I am improving)

IF value from current table is greater than value from the StaticValues table, use the value from StaticValues table.

Ryan_Mortimer

With this expression, I received the following error:

SELECT has invalid inputs

I then adjusted it to:

SELECT(StaticValues[BILLING DATE],true)

and received this error message

The expression is valid but its result type ‘List’ is not one of the expected types: Date

Marc_Dillon

If there will always be only a single record in the table, you can use:

ANY( StaticValues[Billing Date] )

I’d suggest searching these forums for more info on how to use IF statements. There will be plenty of examples.

Steve

Ryan_Mortimer

Thanks Steve!

It’s always a smile from me when I see a reply from you.

The community is a better place thanks to people like you!