Addcolumns dax function

[chahineatallah]

Hello

I did a calendar table by using 

Cal = CALENDAR(MIN(Missions[Date]),MAX(Missions[Date]))

then when i tried to addcolumns using 

month = ADDCOLUMNS(Cal,"M",MONTH(cal[date])), its giving an error telling that expression refers to multiple columns

isnt addcolumns supposed to evaluate formula on row by row?

please find below link to the file

https://catallah-my.sharepoint.com/:u:/g/personal/chahineatallah_catallah_onmicrosoft_com/EZqp0W5O0t... 

thanks

[amitchandak · Accepted solution]

@chahineatallah , Hope this is a new table

month = ADDCOLUMNS(Cal,"M",MONTH(cal[date]))

If you need in same table

Calendar =

var _ Cal = CALENDAR(MIN(Missions[Date]),MAX(Missions[Date]))

return

ADDCOLUMNS(Cal,"M",MONTH(cal[date]))

another example

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

Full calendar code

Date = 
var _Max =  max(max(Sales[Sales Date]), max(Sales[Deilvery Date])) //today() //
var _cal =  
ADDCOLUMNS( CALENDAR(date(2018,01,01), eomonth(_Max,12 - month(_Max)))
,"Month Year" , FORMAT([Date], "MMM-YYYY")
, "Month Year Sort", Year([Date]) *100 + Month([Date]) //FORMAT([Date], "YYYYMM")
, "Month", FORMAT([date], "mmmm")
,"Month No" , MONTH([Date])
,"Qtr Year", format([Date],"\QQ YYYY")
, "Qtr Year Sort", format([Date], "YYYYQ")
, "Year" , year([Date])
, "WeekDay", WEEKDAY([Date],2)
, "WeekDay Name", FORMAT([Date], "ddd")
,"Week Num", WEEKNUM([date],2) 
,"Week Year",  year( [Date])*100 +  WEEKNUM([date],2) 
, "Month Start Date", EOMONTH([Date],-1)+1 
, "Month End Date", EOMONTH([Date],0) 
, "Year Start Date", EOMONTH([Date],-1*month([Date]))+1 
, "Year End Date", EOMONTH([Date],12 -1*month([Date]) ) 
, "Qtr Start Date", Var _month = mod(month([Date]),3) 
                    var _sub = if(_month =0, -3, -1*_month ) 
                    return EOMONTH([date], _sub)+1
, "Qtr End Date", Var _month = mod(month([Date]),3) 
                    var _sub = if(_month =0, 0, 3- _month ) 
                    return EOMONTH([date], _sub)
, "Week Start Date", [date] - WEEKDAY([date],2) + 1 
, "Week End Date", [date] - WEEKDAY([date],2) + 7 
, "Week Start Date (Tue)", var _day = WEEKDAY([date],2) return if( _day >=2  , [Date] - WEEKDAY([date],2) + 2, [Date] - WEEKDAY([date],2) -5)
, "FY Start", if(MONTH([date]) <4, date(year([Date])-1,4,1) , date(year([Date]),4,1))
, "FY End", if(MONTH([date]) <4, date(year([Date]),3,31) , date(year([Date])+1,3,31)),
"Half Year No",  QUOTIENT( datediff(EOMONTH([Date],-1*month([Date]))+1 ,  EOMONTH([Date],-1)+1 , MONTH),6)+1 ,
"Year Half", year([Date]) *100+  QUOTIENT( datediff(EOMONTH([Date],-1*month([Date]))+1 ,  EOMONTH([Date],-1)+1 , MONTH),6)+1 ,
"Half Year Start" , EOMONTH([Date],-1*( Mod( datediff(EOMONTH([Date],-1*month([Date]))+1 ,  EOMONTH([Date],-1)+1 , MONTH),6))-1) +1,
"Work Day", if(WEEKDAY([Date],2)>=6,0,1),
"Work Date",  if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
)
return ADDCOLUMNS( _cal,
"Month Rank", rankx(_cal,[Month Start Date],,ASC,Dense),
"Month Day", DATEDIFF([Month Start Date],[date], day)+1,
"Week Rank", RANKX(_cal,[Week Start Date],,ASC,Dense),
"Half Rank",  RANKX(_cal,[Half Year Start],,ASC,Dense),
"Day of half", datediff([Half Year Start], [date], day)+1,
"Work Day Rank", if(ISBLANK([Work Date]), BLANK(), RANKX(_cal,[Work Date],,ASC,Dense) ),
"Cont Work Date", if([Work Day]=0,maxx(FILTER(_cal,[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
)