Forum Discussion

rodneyc8063_1's avatar
rodneyc8063_1
Icon for Helper V rankHelper V
7 years ago

Dynamic Dates in a Filter/Slicer?

We currently use OBIEE as our reporting tool and I am exploring Power BI.

 

One thing that we do with our reports is we change our dates "dynamically". So for example if the date is between Sept 1 to Dec 31, we would call this "Fall". Then our dashboard would have a prompt default to that Fall value and every time someone logs in during this time period the reports are set to Fall. 

 

Then when it hits Jan 1 to April 1, we flip over to "Winter".

 

This is done without end user interaction, the values flip and would be used in the report filters so the reports are pre set when a user logs in, but they can change it as needed.

 

I was wondering, is there any way to do this in a slicer/filter? 

 

So we would want someone to be able to log into the PBI service online. During the certain time periods, we would want the reports to "default" to certain values. The user can still change these values if they wanted to, but its just a nice touch to be able to let the end user hit the ground running when first logging into the report.

Need Help
Tips and Tricks

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi rodneyc8063_1 

    It is possible to filter data shown by the visuals on the reports.

    My test is as below, assume 9~12:fall, 1~3:winter,4~6:spring,7~8:summer

    create columns in your table

    year = YEAR(Sheet5[date])

month = MONTH(Sheet5[date])

day = DAY(Sheet5[date])

    create measures

    today_date= today()

four parts =
SWITCH (
    TRUE (),
    MAX ( Sheet5[month] ) >= 9
        && MAX ( Sheet5[month] ) <= 12, "fall",
    MAX ( Sheet5[month] ) >= 1
        && MAX ( Sheet5[month] ) <= 3, "winter",
    MAX ( Sheet5[month] ) >= 4
        && MAX ( Sheet5[month] ) <= 6, "spring",
    MAX ( Sheet5[month] ) >= 7
        && MAX ( Sheet5[month] ) <= 8, "summer"
)


today's part = CALCULATE([Measure 2],FILTER(ALL(Sheet5),[date]=[Measure]))

    flag = IF([four parts]=[today's part]&&YEAR([today_date])=MAX(Sheet5[year]),1,0)

    if you want to show data in this period, add flag in the visual level filter of the table visual, thus, it will show data from 2019/1~today

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • rodneyc8063_1's avatar
      rodneyc8063_1
      Icon for Helper V rankHelper V
      7 years ago

      Hi Maggie/ v-juanli-msft 

       

      Thanks for the suggestion and attaching the pbix file, if you dont mind I had a few more questions

       

      I was a bit confused by your code where you mentioned

       

      today's part = CALCULATE([Measure 2],FILTER(ALL(Sheet5),[date]=[Measure]))

      Im guessing from the workbook you actually meant this

      today's part = CALCULATE([four parts],FILTER(ALL(Sheet5),[date]=[today_date]))

      When I tried to open your PBIX file though I got a blank table as shown in the following below 

       

      Not sure why the table is showing up blank?

       

      Also, silly question but how did you even first create the [date] column? And how did you create the [value] sequential column?

       

      Sorry for the silly questions, but Im just starting out in Power BI and trying to self learn all this

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi rodneyc8063_1 

        Yes, you are right.

        today's part = CALCULATE([four parts],FILTER(ALL(Sheet5),[date]=[today_date]))

        Why you see blank when opening the file is that 

        I use "today( )' function in [today_date] measure to get today's date,

        So today, it will show 2019/3/26, 

        but in my test file, there is no data with 2019/3/26,

        You could replace  TODAY() with DATE(2019,1,1) or any other dates to make a test.

        today_date = TODAY()