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.

ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
4 years ago

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

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