Forum Discussion
Newie Question about splitting columns
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 ?
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])returnADDCOLUMNS(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.
1 Reply
- DataZoe
Microsoft Employee
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])returnADDCOLUMNS(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.