Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DATEADD in calculated table cannot use column

Hello,

 

I have following code that creates calculated table with dates for 12 months rolling period.

For some reason I cannot use DATEADD function using column (it gets underlined as error and table does not materialize). I have this stupid workaround to use just [YearMonthTrunc_B]>([YearMonthTrunc_A]-365) but this is causing some issues for leap-years.

 

Is there something better to use than DATEADD?

 

Thanks for any response,

Zuzana

 

Table = 
var monthrange = SELECTCOLUMNS( ADDCOLUMNS(SUMMARIZECOLUMNS('_Date - renewal'[YearMonthTrunc])
                                ,"YearMonth",FORMAT('_Date - renewal'[YearMonthTrunc],"YYYY/MM"))
                 ,"YearMonth_A",[YearMonth]
                 ,"YearMonthTrunc_A",[YearMonthTrunc])
var rollingperiod = SELECTCOLUMNS( ADDCOLUMNS(SUMMARIZECOLUMNS('_Date - renewal'[YearMonthTrunc])
                                   ,"YearMonth",FORMAT('_Date - renewal'[YearMonthTrunc],"YYYY/MM"))
                 ,"YearMonth_B",[YearMonth]
                 ,"YearMonthTrunc_B",[YearMonthTrunc])
                                  
return
FILTER(CALCULATETABLE(CROSSJOIN(monthrange,rollingperiod))
       ,AND([YearMonthTrunc_B]<=[YearMonthTrunc_A]
       ,[YearMonthTrunc_B]>DATEADD([YearMonthTrunc_A],-12,MONTH))
       )
            

Table '_Date - renewal' is also calculated:

_Date - renewal = 
ADDCOLUMNS (
CALENDAR (min(data[start_date]),TODAY()),
"YearMonthTrunc", date(year([date]),month([date]),1),
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Functions that work with dates need input that is a DATE (and even though sometimes type coercion kicks in, you should NEVER rely on that), so please make sure you have the right input into your functions. What's more, some of them need input in the form of a column with specific data types, some work on pure dates (disconnected from any tables). For the correct syntax and data types, please refer to DAX Guide. You'll find everything you need in there.

 

By the way...

 

var __today = TODAY()
var __todayBackInTime = EDATE( __today, -12 ) 
return
	{ __todayBackInTime }

 

Best

Darek

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Functions that work with dates need input that is a DATE (and even though sometimes type coercion kicks in, you should NEVER rely on that), so please make sure you have the right input into your functions. What's more, some of them need input in the form of a column with specific data types, some work on pure dates (disconnected from any tables). For the correct syntax and data types, please refer to DAX Guide. You'll find everything you need in there.

 

By the way...

 

var __today = TODAY()
var __todayBackInTime = EDATE( __today, -12 ) 
return
	{ __todayBackInTime }

 

Best

Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors