Query and Calculated Column (Date and Time as well) Help

[warrenpoh]

Hi,

I have a series of transactions as a data feed from a time and cost programme.  What I would like to do is to take the data on a given day and allocate a flag to the 'live' transactions at that day.  I will then use these flags to undertake analysis next month when time sheets close off to determine how long the transaction has been live.

What I think I needed to do was to add a calculated column (essentially saying IF ( transaction = live, "live", BLANK() ).

As this is the first month that will work - but I don't watn the column to change value if during the month befoer the end of next month, then transaction is set to "billed" by the time and cost programme.

Essentially I want a calculated column that can be 'cut and paste values" over top of itself.

Help?

[v-caliao-msft]

Hi @warrenpoh,

Do you have a time table in your data model? Maybe you can use Lookupvalue to achieve this requirement. It returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…) 

Please provid us the details table structure of your tables and some sample data of them, so that we can make furhter analysis.

Regards,

Charlie Liao