Forum Discussion

ROYMMM's avatar
ROYMMM
Icon for Advocate II rankAdvocate II
6 years ago

Hide future months in matrix

Hi,

 

I have a report with a matrix showing the availability of services. Every column represents a list of availability percentages of the respective services for a specific month. The matrix should only show the months of the current year. (this is set in the filter panel) In the screenshot all months for the current year are shown and I only want the past months to be shown. Can someone help me?

 

 

Thanks!

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    ROYMMM you should add a calendar dimension in your model which is a best practice to for any time intelligence based reporting, add month offset column in that table with datediff and month interval like below,

     

    Month Offset = 
DATEDIFF ( Calendar[Date], TODAY(), MONTH )

     

    Above column will make current month as zero (as of today feb 11, 2020), Feb 2020 will be 0 and Jan 2020 will be -1 and so on, and future months will be positive values, March 2020 will be 1, April 2020 will be 2 etc.

     

    Now you can use this Month offset filter as page level or report level filter,  where month offset is less than equal to 0, and it will alway show current and past month and you can change this filter value as per your need.

     

    Or with 2nd trick, using calendar dimension, as relative date filtering link here

     

    All in all, you need calendar/date dimension in your model.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The question was about how to remove future months from a matrix, not how to get the current one.

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi ROYMMM,

     

    I assume you have some sort of date table. In your date table, do you have a column for the first date of the month? If not, create it like this

    LastDateOfMonth = endofmonth('Date table'[Date])


    Then create a new column like this

    IsFutureMonth = if(today()>LastDayOfMonth;1;0)


    Now for you matrix visual, add IsFutureMonth to the filter pane, and set the filter to IsFutureMonth=0.

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      If this calculated column is used as.a filter less than 0, it doesn't show anything. If it's equal to 0, it shows only the current month. If it's greater than 0, it shows all months, including future ones.

       

      I have tried your "solution" on two matrices, with and without YOY columns, and the result is the same.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI ROYMMM,

    You can add if statements to your measure formula to check date and skip calculations on records that greater than current date. If full of row/column does not contain valid values(no blank value), they will be hidden by visual itself.

    If you confused about coding formula, can you please share some dummy data to test? It is hard to clearly your table structure form matrix snapshot.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng