Matrix showing only Future Months

[GM_AUS]

Hi!

Here is the link to Fake Data and Report https://drive.google.com/drive/folders/12psHWMA2tbSyAyZoTj90OOZnl49_qOck?usp=sharing 

On my report I have a matrix and I would like to show the columns for future months only, and even if they have no values in it.

For instance, if we are in May 2022, I would like the matrix to show only the columns starting with May 2022 onwards. I don't want to see the column for January, February, March and April 2022.

However, I would like to be able to see values for January, February, March and April 2023 (next year), but I don't want to group the values of 2022 with 2023. I want to be able to see them separately.

This is what I have now:

This is what I would like to see:

I managed to do it by hiding the columns manually (Visualizations Pane >> Visual >> Column Headers >> Text >> Text Wrap OFF.

However, this would mean I would have to hide a month column everytime that a month has ended. This is not practical and I would like it to be automatic.

Can anybody help me?

Thank you in advance!

Gabriela

[v-kkf-msft · Accepted solution]

Hi @GM_AUS ,

You need to add a complete Calendar table. 

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "MonthNum", MONTH ( [Date] ),
    "Is futuremonth", IF ( [Date] > EOMONTH ( TODAY (), -1 ), "Yes", "No" )
)

Then use the Calendar[Month] column in the matrix and filter it using the Calendar[Year] and Calendar[Is futuremonth] fields.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GM_AUS]

Thanx a lot! That worked! Now I can see all columns that are empty and the values of year 2022 and 2023 are not grouped. 🙂

[ddpl]

Please try this:

Step 1 : You should create new table with filtering column "isfuture month" =yes as per below snap

"

New Table for where 'Is futuremonth is "Yes" = CALCULATETABLE(SUMMARIZE('Projects by Domain','Projects by Domain'[Date],'Projects by Domain'[Month],'Projects by Domain'[Perc Allocation],'Projects by Domain'[Project],'Projects by Domain'[Role],'Projects by Domain'[Staff],'Projects by Domain'[Year],'Projects by Domain'[isFutureMonth]),FILTER('Projects by Domain','Projects by Domain'[isFutureMonth] = "Yes"))"

Step2 : now create matrix as per below using new table.

If its worked plz mark as accepted as solution.

[GM_AUS]

Hi VipulGahiya! Thank you for your reply! It is actually not necessary to create another table for that. You can use the Filters' pane to set up a filter based on the column isFutureMonth = "yes". We get the same result.

However, from your solution it is still not possible to see the columns with no values. Notice that on your screenshot,  from August 2022 to January 2023, nothing is shown. But I need to be able to see when a column is empty as well 😞

To solve this, I tried Visualisations pane >> Columns >> clicked on the down arrow >> "Show items with no data". See below:

But then, another problem:  it shows everything, starting from January 2022. See screenshot below:

So, I am not sure how to fix that 😞 

Thank you for your help.

Gabriela

[danextian]

Hi @GM_AUS ,

There are multiple approaches to your use case.
One: Create a calculated column that will check whether the month in [Date] is greater than the today's month and use that as a visual filter or as a filter condition in a measure.

Is a Future Month? =
VAR __CURRENT =
    VALUE ( FORMAT ( TODAY (), "YYYYMM" ) )
RETURN
    VALUE ( FORMAT ( 'Projects by Domain'[Date], "YYYYMM" ) ) > __CURRENT

And then a measure to show future months even those that don't have a value:

Sum of Per Alloc - Future Months =
CALCULATE (
    SUM ( 'Projects by Domain'[Perc Allocation] ) + 0,
    //+0 to show blanks as zeroes
    FILTER (
        'Projects by Domain',
        'Projects by Domain'[Is a Future Month?] = TRUE ()
    )
)

 Two: Use just a measure

Sum of Per Alloc as a measure - Future Months = 
VAR Day1NextMonth =
    EOMONTH ( TODAY (), 0 ) + 1
RETURN
    CALCULATE (
        SUM ( 'Projects by Domain'[Perc Allocation] ) + 0,
        FILTER ( 'Projects by Domain', 'Projects by Domain'[Date] >= Day1NextMonth )
    )

I have tested both approaches in a matrix. Although they calculate the value for future months, they will still show past months but as zero.  So as a workaround, you may use the calculated column in the first approach to filter the visual or the page and, instead of using the month from date hierarchy, use a calculated column that will show both month and year.

Here's the pbix -  https://drive.google.com/file/d/1cig610F1dVHHj5Av_86c3Sk56Ak7yqY2/view?usp=sharing 

[GM_AUS]

Hi danextian!

Thank you for your reply. I have checked your file and followed your instruction: "as a workaround, you may use the calculated column in the first approach to filter the visual or the page and, instead of using the month from date hierarchy, use a calculated column that will show both month and year."

However, it seems that I can't see the columns refering to september, october, november and december 2022 because they are empty. But it is important for me to be able to see when a column is empty. This is what I can see:

Note that "show items with no data" is selected for Columns >> YYYYMM.

If you have any idea, let me know. I would really appreaciate your help.

Thank you!

Gabriela.