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 

After my test by adding Column Months Name YTD and Column Year Month Number and sort Months Name YTD by Year Month Number like you,

I can not reproduce the error.

So could you please share your pbix or the data sample and formula of your Column Months Name YTD and Column Year Month Number.

You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

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

    Hi@Tamir 

    After my test on your demo , value ”Blank” in column “Months to Date” has many different corresponding values in column “MonthYearNum”,

    So it occurred this error.

    You can try to do these follow my steps:

    Step 1:

    Add two columns:

    One is to filter :

    Months to Date(filter) = 
IF (
    MONTH ( 'Calendar'[Date] ) <= MONTH ( TODAY () )
        && YEAR ( 'Calendar'[Date] ) = YEAR ( TODAY () ),
    FORMAT ( 'Calendar'[Date], "mmm" ) & "-"
        & FORMAT ( 'Calendar'[Date], "yy" ),
    BLANK ()
)

    One is to be sorted and dragged  into visual:

    Months to Date =FORMAT ( 'Calendar'[Date], "mmm" ) & "-"
        & FORMAT ( 'Calendar'[Date], "yy" )

    Step 2:

    Sort Months to Date by MonthYearNum and drag it into visual

    drag  Months to Date(filter) into slicer and page level filter.

     

    here is Demo, please try it .

    https://www.dropbox.com/s/v80cfe7eahr4mws/Sort%20Month%20to%20Date.pbix?dl=0

     

    Reference: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

     

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you v-lili6-msft

       

      I was wondering if it possible to sort the column 

      Months to Date(filter)

      by another column.  
      I do not want to filter the whole report/page by that column as other visuals need to be shown for other dates as well.

       

      Thank you,

      Tamir

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        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