Forum Discussion
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 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
rollingzep Try:
Column C = SWITCH(TRUE(), [Transaction Date] = BLANK(), 1, ( [Report Date] - [Transaction Date] ) * 1. <= 30, 1, BLANK() )
4 Replies
rollingzep Try:
Column C = SWITCH(TRUE(), [Transaction Date] = BLANK(), 1, ( [Report Date] - [Transaction Date] ) * 1. <= 30, 1, BLANK() )I am getting an error
I do have asofdate and TransactionEndDate and I am trying to create a new Measure.
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() )
