Forum Discussion

gordway's avatar
gordway
Frequent Visitor
5 years ago
Solved

Numeric Slider Slicer

I have a slider slicer for months as shown in the image below. I would like it so the report opens YTD to the current month whenever someone views the report. Is this possible to do?

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi gordway 

    I think you have build a flag column in Month Table. You can build a flag column in Year Table as well. Then add two flag columns into Filter Fields in your visual and set them to show items when the value is not 0.

    Year YTD (minus 7 days) = 
IF (
    MONTH ( TODAY () ) = 1
        && DAY ( TODAY () ) <= 7
        && 'Year'[Year]
            = YEAR ( TODAY () ) - 1,
    'Year'[Year],
    IF ( 'Year'[Year] = YEAR ( TODAY () ), 'Year'[Year], 0 )
)

    Relationship:

    Month[Month] to Table[Month], Table[Year] to Year[Year]

    Add two YTD columns into Filter Field in your visual and set them to show items when value is not 0. You see we can only see values in YTD range.

    We can filter month in YTD range.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    gordway Depends on your dataset. Does that represent months? You could create a calculated column that figures out if the row should be included in YTD calculations. Really hard to be specific. 

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • gordway's avatar
      gordway
      Frequent Visitor
      5 years ago

      Greg_Deckler Hey Greg. Thanks for your reply.

      But yes, the slider is for the numeric months. My goal is to have the slider open by default whenever the report is viewed to, for example, 1 (January) through the current month or 8 (being august obviously). My dataset is simple for this slicer because I just have relationships to other tables that are broken down by year and month.

      I added a calculated column for YTD (for my purposes I wanted a 7 day lag). See below for what I have so far. I am hoping I am on the right path. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi gordway 

        I think you have build a flag column in Month Table. You can build a flag column in Year Table as well. Then add two flag columns into Filter Fields in your visual and set them to show items when the value is not 0.

        Year YTD (minus 7 days) = 
IF (
    MONTH ( TODAY () ) = 1
        && DAY ( TODAY () ) <= 7
        && 'Year'[Year]
            = YEAR ( TODAY () ) - 1,
    'Year'[Year],
    IF ( 'Year'[Year] = YEAR ( TODAY () ), 'Year'[Year], 0 )
)

        Relationship:

        Month[Month] to Table[Month], Table[Year] to Year[Year]

        Add two YTD columns into Filter Field in your visual and set them to show items when value is not 0. You see we can only see values in YTD range.

        We can filter month in YTD range.

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.