Forum Discussion
Strange DATE Function Behaviour
Hi all, looking for a sanity check and hopefully an explanation on the below. I have a Date table with: Column Name (Column Type) Date (Date) Year (Whole Number) MonthOfYear (Whole Number) ...
Your calculated column return a month of zero whenever the date is in January
The error is in the "months" parameter of the DATES function - the result is zero.
My solution is to add two intermediary steps:
Step1:
NewColumn_Step1 =
CALCULATETABLE(
LASTDATE( 'Date'[Date] ),
PREVIOUSMONTH( 'Date'[Date] )
)
Step2:
New Column_Step2 =
IF( ISBLANK( 'Date'[NewColumn_Step1] ), EOMONTH( 'Date'[Date], -1 ), 'Date'[NewColumn_Step1] )
Final:
NewColumn =
DATE(
YEAR( 'Date'[New Column_Step2] ),
MONTH( 'Date'[New Column_Step2] ) - 1,
DAY( EOMONTH( 'Date'[New Column_Step2], -1 ) )
)