Newie Question about splitting columns

[EdM1980]

I'm brand new to Power Bi and I'm running into an issue due to lack of knowledge. I've searched online but to be honest I dont even know what I should be search for. My issue is I have a column from an auto-generated report with start dates for my work (they go back to the 80's) the format is in short date (3-Nov-20). Is there away of using Power Bi to break those out into months and years ?

[DataZoe · Accepted solution]

@EdM1980 there are a few approaches! You can create the columns in Transform Data, or as measures in the table, or introduce a Date table to your model (creating a relationship between the Date table and your date column). in all scenarios, it's important your date column is properly changed to a Date datatype. 

For the calculated columns you can use:

Month =  DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )

Year = DATE ( YEAR ( [Date] ), 1, 1 )

Then you can format it to look like a month or month (instead of a date). Bonus here is it sorts properly. 

To create the date table, you go to Modeling -> New Table and use the following DAX:

Date =

var startD = min('Table'[Date])

var endD = max('Table'[Date])

return

ADDCOLUMNS(

CALENDAR(startD,endD),

"Month", date(year([Date]),month([Date]),1),

"Year", date(year([Date]),1,1),

"Week",[Date] - WEEKDAY ( [Date], 1 ) + 1,

"Monthly Week Number", WEEKNUM([Date],1) - WEEKNUM(DATE(year([Date]),month([Date]),1),1)+1,

"Yearly Week Number", WEEKNUM([Date]),

"Month Relative Number", DATEDIFF(today(),[Date],MONTH),

"Month Relative", SWITCH(DATEDIFF(today(),[Date],MONTH),0,"Current Month",-1,"Last Month",1,"Next Month",if(DATEDIFF(today(),[Date],MONTH)>0,"Future Months","Past Months")),

"Month Relative Sort Order", SWITCH(DATEDIFF(today(),[Date],MONTH),0,3,-1,4,1,2,if(DATEDIFF(today(),[Date],MONTH)>0,1,5)),

"Year Month Num",MONTH([Date])

)

Then create a relationship between the tables on dates.