Forum Discussion
Pivoted Matrix - Future Monthly Columns With No Data Not Showing
I have a pivoted matrix where future columns in the current year are not showing. I am using measures for the columns and even added a + 0 at the end, in hopes that it would show up.
This visual has two matrixes. The top one is not pivoted, but the bottom one is.
I have checked the "Show Items with No Data" for the Month Name column and above is what I get.
Any ideas on how I can show the months of July through Dec 2025 would be greatly appreciated!
Hi hallmarke14
I agree with the approach suggested by Akash_Varuna . Here's a comprehensive solution to ensure future months (July-December 2025) appear in your matrix visual:
1. Verify and Extend Your Date Table
Problem: The most common reason future months don't appear is an incomplete date table.
Solution:
let StartDate = #date(2020, 1, 1), // Adjust start year as needed EndDate = #date(2025, 12, 31), // Must include all of 2025 Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), // Add additional date columns (Year, Month, etc.) as needed #"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Added Month" = Table.AddColumn(#"Added Year", "Month", each Date.Month([Date]), Int64.Type), #"Added MonthName" = Table.AddColumn(#"Added Month", "MonthName", each Date.MonthName([Date]), type text) in #"Added MonthName"2. Properly Configure Date Table
Right-click your date table → "Mark as date table"
Select the appropriate date column
Ensure relationships to fact tables are active and properly configured
3. Create a Month Dimension Table (Alternative Approach)
MonthsDisplay = DATATABLE( "MonthNumber", INTEGER, "MonthName", STRING, "MonthNameShort", STRING, "Quarter", STRING, { {1, "January", "Jan", "Q1"}, {2, "February", "Feb", "Q1"}, {3, "March", "Mar", "Q1"}, {4, "April", "Apr", "Q2"}, {5, "May", "May", "Q2"}, {6, "June", "Jun", "Q2"}, {7, "July", "Jul", "Q3"}, {8, "August", "Aug", "Q3"}, {9, "September", "Sep", "Q3"}, {10, "October", "Oct", "Q4"}, {11, "November", "Nov", "Q4"}, {12, "December", "Dec", "Q4"} } )
4. Modify Measures to Handle Future Months
Sales Measure = VAR CurrentDate = TODAY() VAR SelectedDate = MAX('Date'[Date]) VAR CurrentYear = YEAR(CurrentDate) RETURN IF( SelectedDate <= CurrentDate, [Actual Sales], // Return actual values for past/current dates IF( YEAR(SelectedDate) = CurrentYear, 0, // Return 0 for future months in current year BLANK() // Blank for future years (or adjust as needed) ) )
5. Check Visual-Level Filters
Ensure you don't have any filters hiding future months.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
3 Replies
- Akash_Varuna
Super User
Hi hallmarke14 Ensure your Date table includes all months, even future ones, and is marked as a Date Table. Connect it properly to your data model, and update your measures to include a + 0 logic to display zeros for months with no data. Enable the "Show Items with No Data" option for the Month Name column in your matrix.
- Elena_Kalina
Solution Sage
Hi hallmarke14
I agree with the approach suggested by Akash_Varuna . Here's a comprehensive solution to ensure future months (July-December 2025) appear in your matrix visual:
1. Verify and Extend Your Date Table
Problem: The most common reason future months don't appear is an incomplete date table.
Solution:
let StartDate = #date(2020, 1, 1), // Adjust start year as needed EndDate = #date(2025, 12, 31), // Must include all of 2025 Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), // Add additional date columns (Year, Month, etc.) as needed #"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Added Month" = Table.AddColumn(#"Added Year", "Month", each Date.Month([Date]), Int64.Type), #"Added MonthName" = Table.AddColumn(#"Added Month", "MonthName", each Date.MonthName([Date]), type text) in #"Added MonthName"2. Properly Configure Date Table
Right-click your date table → "Mark as date table"
Select the appropriate date column
Ensure relationships to fact tables are active and properly configured
3. Create a Month Dimension Table (Alternative Approach)
MonthsDisplay = DATATABLE( "MonthNumber", INTEGER, "MonthName", STRING, "MonthNameShort", STRING, "Quarter", STRING, { {1, "January", "Jan", "Q1"}, {2, "February", "Feb", "Q1"}, {3, "March", "Mar", "Q1"}, {4, "April", "Apr", "Q2"}, {5, "May", "May", "Q2"}, {6, "June", "Jun", "Q2"}, {7, "July", "Jul", "Q3"}, {8, "August", "Aug", "Q3"}, {9, "September", "Sep", "Q3"}, {10, "October", "Oct", "Q4"}, {11, "November", "Nov", "Q4"}, {12, "December", "Dec", "Q4"} } )
4. Modify Measures to Handle Future Months
Sales Measure = VAR CurrentDate = TODAY() VAR SelectedDate = MAX('Date'[Date]) VAR CurrentYear = YEAR(CurrentDate) RETURN IF( SelectedDate <= CurrentDate, [Actual Sales], // Return actual values for past/current dates IF( YEAR(SelectedDate) = CurrentYear, 0, // Return 0 for future months in current year BLANK() // Blank for future years (or adjust as needed) ) )
5. Check Visual-Level Filters
Ensure you don't have any filters hiding future months.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Thank you, Elena! I appreciate your detailed response! 🙂
