Date calculation

[rollingzep]

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 to 30 days of report date or 

insert value 1 in Col C if Transaction Date is null.

I know how to do it in Excel VBA but I am new to Power BI.  In VBA, I use

If DateDiff("d", FirstDate, SecondDate) <= 30 Then ws.Range("C" & i) = 1, for the first condition.

Not sure how to create a new Measure using Datediff.

Also, I loop through the entire range as in LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row).

what is the equivalent in BI?

TIA

[Greg_Deckler · Accepted solution]

@rollingzep Try:

Column C = 
  SWITCH(TRUE(),
    [Transaction Date] = BLANK(), 1,
    ( [Report Date] - [Transaction Date] ) * 1. <= 30, 1,
    BLANK()
  )

[rollingzep]

I am getting an error

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

[Greg_Deckler]

@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]

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?