Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to make a today,yesterday and 2 days before button.

Hi

 

I need to create 3 buttons: Today, Yesterday and 2 days before.
I tried using bookmarks but only succeeded in making Today button by using my date slicer relative date set to today - save to bookmarks and make a new button linked to that bookmark - problem I can't get back to original state anymore. Also this way doesn't work for yesterday or 2 days before because I can't set my date slicer to show only yesterday etc. ( should be dynamic not manually set to yesterday, since tomorrow today is yesterday )

I can make new columns with required dates but can't make a button out of columns.

I might add the report is in direct query.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 
You can take a try this method that does not use bookmarks and buttons.

The steps are as follows:

1.Create a custom  as below:
Power query:

 

=if [Date] = Date.From(DateTime.LocalNow()) then "Today" else if [Date] =Date.From(Date.AddDays(DateTime.LocalNow(),-1)) then "Yesterday" else if [Date] =Date.From(Date.AddDays(DateTime.LocalNow(),-2)) then "2 Days Before" else [Date]

 

20.png

16.png

2.Then enter a table as below

17.png

3. Build relationships between tables,then you can drag the fields into the treemap to filter the data to achieve a similar effect
19.png

 

Best Regards,
Community Support Team _ Eason

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

I prefer @v-easonf-msft solution but it requires import mode and daily dataset refresh - keep that in mind.

v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 
You can take a try this method that does not use bookmarks and buttons.

The steps are as follows:

1.Create a custom  as below:
Power query:

 

=if [Date] = Date.From(DateTime.LocalNow()) then "Today" else if [Date] =Date.From(Date.AddDays(DateTime.LocalNow(),-1)) then "Yesterday" else if [Date] =Date.From(Date.AddDays(DateTime.LocalNow(),-2)) then "2 Days Before" else [Date]

 

20.png

16.png

2.Then enter a table as below

17.png

3. Build relationships between tables,then you can drag the fields into the treemap to filter the data to achieve a similar effect
19.png

 

Best Regards,
Community Support Team _ Eason

 

@v-easonf-msft @Syndicate_Admin  Please can you assist with the above i currently have customers in different time zones , although when deploying this solution it works for a few hours whilst the customer is within the same day, although when rolling over the values go blank .. So if i had to select Today it would blank out as my local time is +2 gmt SA time. Resulting in the model reading system time and failing to load the data in the customer zone. Here is an example of what i am trying to achieve.

I have added a calculated Column with the customer offset with a Parameter.

PBICalNaidoo_0-1675252286142.png

I have then created a startdate column of my startimestamp.

PBICalNaidoo_1-1675252400508.png

I have then written the following Dax.

PBICalNaidoo_2-1675252459438.png

How do i get these buttons to work with the -7 Arizona timezone for example so that when rollover occurs to the next day the data stays within that timezone

 

 




Hello @Syndicate_Admin , I just had the same concern to add a button that filters the data of yesterday, replicates everything you did and you know that when I want to drag the field "Index" for the graph Treemap does not let me

lhreinoso_0-1674141332564.png

It is configured as "text" type since it is in the same way in the pbix that you attached, what may be happening?

lbendlin
Super User
Super User

"I need to create 3 buttons: Today, Yesterday and 2 days before."  Are you sure? Have you considered doing a horizontal slicer instead? Looks just like buttons.

"I might add the report is in direct query"  That is no longer a problem. You can have in-row calculated columns in direct query sourced tables.

Anonymous
Not applicable

I already have a date slicer but for some reason bosses want those buttons too, I don't know why either because slicer works just fine. I might aswell talk to them that its pointless.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.