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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
croberts21
Continued Contributor
Continued Contributor

Matrix: Sum values by months as columns not working

I have Free PBI Desktop v2.105 (64 bit free version May 2022) on 64-bit Windows Server 2019 Datacenter v1809 on a remote PC.

I made a matrix to access some test data in an Excel file. Important fields are: "Date of error" (which PBI recognizes as a date though one test date is blank), "Total value", "Dept." I want to make a matrix with the departments down the far left side, and dates in MMMM YYYY Format as column headers, and the values will sum "Total value" by month and department. So I made a column (not measure) which is: "YYYY-MM = Test2022[Date of error]" and it's formatted in the top menu bar as YYYY-MM.

croberts21_0-1656334873336.png

 

And my matrix fields: 

croberts21_1-1656334911862.png

 

 

The test data has several entries for June 2021, and several for July 2021. But the matrix does not group Total value together by month and year (YYYY-MM), see screenshot of what I get.

If I try to create a measure for the formatted Date of error column, like YYYY-MM, PBI does not let me enter the date field when making the measure.

I've watched 6-7 tutorial videos totalling 12+ hours. What am I missing here? Why won't PBI sum by the month? Why are the months all separate in the top of the columns?

 

Thank you!

 

P.S. I have a test Excel file and PBIX file if you need it.

 

I got it!

 

# Make a new column called "Month Year". Add to the new column a copy of the Date of error field.
# In the Fields pane, click the new column "Month Year". Under Column tools tab, use tab bar to format as MMMM YYYY.
# Add this date as a column to the matrix.
# In the matrix column, right click the date, click New Group. A dialog window will pop up.
# Add a bin of 1 month. Click OK.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@croberts21 , Formatting of Date will not do, Create a new column

 

Year month  = format([Date], "YYYY-MM")

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@croberts21 , Formatting of Date will not do, Create a new column

 

Year month  = format([Date], "YYYY-MM")

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks, this did work. I'd like to learn from this. But why do I have to use the FORMAT() function (which returns a string)? Why didn't using the Format dropdown list in the PBI FORMAT tab bar work?

 

The report requestor wants to see the months as "June 2021" in date order, not by order of the name of the month. If I use MonthYear = FORMAT([date], "MMMM YYYY") then FORMAT will return a string, not a date value, and the sort order will be wrong.

@croberts21 , when you change the format in the column tool, it is actually the date displayed in that format. This means internally it is still the date

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you. How do I make one month per year across the top of my matrix to show a format like "June 2021" and still have the column headers dates in date order and sum values by month? But without using the FORMAT() function. This would be the correct order for some months: January 2021, February 2021, March 2021, April 2021, May 2021, June 2021, July 2021, August 2021, September 2021, October 2021, ...

This problem above was just my intermediate problem. The real problem is the report viewers want to see the months in the format "June 2021". I already trying several things without using the FORMAT() function. I also searched the internet without finding something that worked.

Or should I make a new question?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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