Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Creating a column with fewer values form months

Hello,   In my calendar table, I have Jan 2015 until Dec 29018.   I would like to put a filter  (slicer) on the page that shows only (!) JAN18 until current Month,   I tried to create a new cal...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    Hi@Tamir

    For value (blank) in this column may have many different corresponding values in other columns, So it can’t be sorted by another column.  

    And filter the whole report/page by this column is just to hide the "Blank" in the visual you don’t want to see, so you can just add a visual level filter on this visual.

     

     

    Best Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
8 years ago
Hi@Tamir 
You can try to use  TODAY()function to determine current month and year to create the column and then pull it to slicer like below:
Column =
IF (
    MONTH ( 'Table'[Date] ) <= MONTH ( TODAY () )
        && YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
    FORMAT ( 'Table'[Date], "mmmm" ) & " "
        & YEAR ( 'Table'[Date] )
)
Result:
 
Best Regards,
Lin
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-lili6-msft

     

    Your DAX solution is wonderful. 

     

    However, How can I sort it by months and not alphabetically?

     

    Using the "Sort By" command in the Modelling produce an error due to the empty cells for future months.

     

    I could format the months as "mm" and get the form of 01,02...etc... but I do want the names (three letters).

    Also, how can I hide the "Blank" months (the future)?

     

    Any idea?

     

    Thank you,

    Regards,

    Tamir

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      8 years ago

      Hi@Tamir 

      Q1:

      You can format the months as "mmm" and get the names and the form of three letters.

      Q2:

      This error is not caused by the empty cells of the future months,

      It could be caused by the duplicates in “Months Months Names YTD”.

      For example :

       

      We sort Index by Column or Column 2, it’s OK.

      But soft Custom by Column or Column 2, it will be wrong.

       

      Q3:

      if you want to hide the "Blank" months, you can drag Column field into Page level filters  and filter (Blank) like below:

       

      Best Regards,

      Lin

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi v-lili6-msft

         

        Q3 and Q1 - Great. Did it.

         

        Q2 - I don't understand. As in your screenshot, I would like to sort column2 ("mmm-yy") by the Index column (in my dimCalendar it would be column MonthYearNumber). 

         

        Thank you,

         

        Regards,

        Tamir