Pivoted Matrix - Future Monthly Columns With No Data Not Showing

[hallmarke14]

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!

[Elena_Kalina · Accepted solution]

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.

[hallmarke14]

Thank you, Elena!  I appreciate your detailed response! 🙂

[Akash_Varuna]

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.