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 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

  • 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

  • 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