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 calculated column with Filter and VALUES but it doesn't work. I get an empty column.

 

 

Months 2018 = CALCULATE(VALUES(dimCalendar[MonthYear]),FILTER(dimCalendar, dimCalendar[Year] = 2018))

 

Thanks in advance for any help,

Tamir

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

9 Replies

  • 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