Forum Discussion

rollingzep's avatar
rollingzep
Regular Visitor
3 years ago
Solved

Date calculation

Hi,   I have two dates in col A, report date and col B, Transaction Date.  I need to populate a new column, C. The condition is,  insert value 1 in Col C if Transaction Date is less than or equal ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    rollingzep Try:

    Column C = 
  SWITCH(TRUE(),
    [Transaction Date] = BLANK(), 1,
    ( [Report Date] - [Transaction Date] ) * 1. <= 30, 1,
    BLANK()
  )
rollingzep's avatar
rollingzep
Regular Visitor
3 years ago

I am getting an error

 

I do have asofdate and TransactionEndDate and I am trying to create a new Measure.

 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

rollingzep If it's a measure then you need to wrap your column references in something like MAX or MIN.

Measure C = 
  SWITCH(TRUE(),
    MAX([Transaction Date]) = BLANK(), 1,
    ( MAX([Report Date]) - MAX([Transaction Date]) ) * 1. <= 30, 1,
    BLANK()
  )
  • rollingzep's avatar
    rollingzep
    Regular Visitor
    3 years ago

    Still getting this error

     

    "Column 'asofdate' cannot be found or may not be used in this expression". is this because another table has the the same field, asofdate? If so, how to qualify using tablename in the Expression? or is it because of the date format?