Forum Discussion

GM_AUS's avatar
GM_AUS
Regular Visitor
4 years ago
Solved

Matrix showing only Future Months

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 co...
Need Help
Tips and Tricks
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
4 years ago

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's avatar
GM_AUS
Regular Visitor
4 years ago

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.