Group dates to Month

Hi, welcome to powerbi. There are two things to do:

1. Make sure you have formatted your date column as a date: Modelling Ribbon/Formatting/Data type

if you stop at this point, You can drag in the date column to say the table visual and PBI will auto group the date into year, quarter, month, day (you can also show all the dates by clicking the little drop down arrow on the values field for the visualisation and selcting [Date])

2. For a better model, you should create a date table. Either connect to your date table in SQL or the like or create one by clicking Modelling/Calculations/New Table and in the formula bar entering this piece of DAX:

Datekey = CALENDAR(DATE(2017,01,01),date(2017,12,31)).

There are other methods but this is the simplest. You can add on columns for years, months, days etc but this is a bare bones solution.

Cheers,

// If this is a solution, please mark as such

Anonymous To add to what Sam said, here is a DAX table that will produce various aggregations of time, including the specific request of Year/Month. Follow the same steps Sam outlined to create the table. Link your date column to the date column in the new table, and you can use the YearMonthShort to produce your table.

Credit for this code goes to a different poster, I just can't find the thread.

Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Hi Anonymous,

Is the column [Period] set to date type? If so, you can refer to this formula to create a calculated table. Since there is no such a date format like this: 'Feb-16' (Mon-Year), you might need to create a new string column to display date in this format.

Test Table =
SUMMARIZE (
    TableName,
    TableName[PResolveDate].[Year],
    TableName[PResolveDate].[Month],
    "Total", SUM ( TableName[Amount] )
)

Date = 'Test Table'[Year] & "-" & 'Test Table'[Month]

Best regards,
Yuliana Gu