cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joylili Frequent Visitor
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
samdthompson Established Member
Established Member

Re: 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

 

View solution in original post

Super User
Super User

Re: Group dates to Month

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


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

v-yulgu-msft Super Contributor
Super Contributor

Re: Group dates to Month

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
samdthompson Established Member
Established Member

Re: 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

 

View solution in original post

Super User
Super User

Re: Group dates to Month

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


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

v-yulgu-msft Super Contributor
Super Contributor

Re: Group dates to Month

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

v-yulgu-msft Super Contributor
Super Contributor

Re: Group dates to Month

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.
rush Member
Member

Re: Group dates to Month

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 396 members 3,901 guests
Please welcome our newest community members: