Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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


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

v-yulgu-msft
Employee
Employee

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]

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
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

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.
v-yulgu-msft
Employee
Employee

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]

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.

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




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


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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.