The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
And my matrix fields:
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.
Solved! Go to Solution.
@croberts21 , Formatting of Date will not do, Create a new column
Year month = format([Date], "YYYY-MM")
@croberts21 , Formatting of Date will not do, Create a new column
Year month = format([Date], "YYYY-MM")
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
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?