Dynamic Dates in a Filter/Slicer?

[rodneyc8063]

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.

[v-juanli-msft]

Hi @rodneyc8063 

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]

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]

Hi @rodneyc8063 

I'm sorry for not providing a completed solution.

Please ignore my previous solution, refer to the following pbix with new solution.

In this solution, i create a dynamic date table, 

Use this function "calendar", you could create a date table from start date to end date, 

in my test, i define the end date is 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.

[v-juanli-msft]

Hi @rodneyc8063 

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

[v-juanli-msft]

Hi @rodneyc8063 

Please note that my formula is applied in measures instead of columns.

You could see "Create measures"/"Create columns"/"Create new table" in the "Modeling" bar,

In my solution, first I Create a new table with the formula,

New date table = 
ADDCOLUMNS(CALENDAR(DATE(2018,1,1),TODAY()),"year", YEAR([date]),"month",MONTH([date]),"day",DAY([date]))

then i create measures 

today_date = today()

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

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

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

If you have any other table, you want to use this date table and slicer in other tables, you could create relationships between these tables based on "date" column.

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.