cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Group dates to Month

hi Everyone,

 

i am a newbie to PowerBi.

 

i have a data source that i have 2 columns. Period and ValueCol.

Period gives dates eg. 2/1/2016, 2/2/2016, 2/3/2016, 2/6/2016 ..... for all months in 2016. ValueCol has values for each date entry. i want to have it as the "desired outcome" as in the picture below

 

2017-04-30 14_48_50-Book1 - Excel.png

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

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

 

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

Highlighted
Super User I
Super User I

@joylili 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" )
)


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Highlighted
Microsoft
Microsoft

Hi @joylili,

 

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]

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Impactful Individual
Impactful Individual

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

 

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

Highlighted
Super User I
Super User I

@joylili 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" )
)


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Highlighted
Microsoft
Microsoft

Hi @joylili,

 

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]

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Microsoft
Microsoft

Hi @joylili,

 

Have you resolved your issue? If so, please kindly mark the corresponding reply as an answer so that some other users can find the solution more easily. If you still have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Hi @v-yulgu-msft @Seth_C_Bauer @samdthompson

Is it possible to group the totals in a column so that it sums up all the values from one column into a  new one by month and year, user & on a task without having to create a new table to achieve the outcome?:




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors