cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic date filter to be implement as on yesterday

Hi,
I have requirement to be shown in report whenever the report is opened in service it has to show yesterday's mtd,then they can search from there .

gaininsight1_0-1624437498049.png

3 different slicers i,m using for year,month and day i need that to change dynamically when i check-in to report in service.
How is it possible ????

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

You can create a MTD measure like below. When a specific date is selected with the slicers, calculate the MTD of that date, otherwise calculate yesterday's MTD. 

MTD = 
VAR _yesterday = TODAY()-1
VAR _endDate = SELECTEDVALUE(DimDate[Date],_yesterday)
VAR _startOfMonth = EOMONTH(_endDate,-1)+1
RETURN
CALCULATE(SUM('Table'[Value]),DATESBETWEEN(DimDate[Date],_startOfMonth,_endDate))

 

Before you publish the report, ensure the slicers don't select a date. After publishing the report to service, enter the report's settings pane and turn on the option Don't allow end users to save filters on this report under Persistent filters. Save the settings. Take note that this setting is working on the whole report.

062501.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

 

 

 

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

You can create a MTD measure like below. When a specific date is selected with the slicers, calculate the MTD of that date, otherwise calculate yesterday's MTD. 

MTD = 
VAR _yesterday = TODAY()-1
VAR _endDate = SELECTEDVALUE(DimDate[Date],_yesterday)
VAR _startOfMonth = EOMONTH(_endDate,-1)+1
RETURN
CALCULATE(SUM('Table'[Value]),DATESBETWEEN(DimDate[Date],_startOfMonth,_endDate))

 

Before you publish the report, ensure the slicers don't select a date. After publishing the report to service, enter the report's settings pane and turn on the option Don't allow end users to save filters on this report under Persistent filters. Save the settings. Take note that this setting is working on the whole report.

062501.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

 

 

 

Anonymous
Not applicable

Hi 
I have requirement to show dynamic change of date,whenever You open the report in service it should be show as on yesterday.
For date I used 3slicers year,month &dayFor suppose if i open report today(12th july 2021) the date should be shown as (11th july 2021),
if i open report on(1st july 2021) the date should be shown as (30th june 2021)

Hi @Anonymous 

 

You can refer to below videos: 

Set Default Slicer Selection to Current Year or Month in Power BI - YouTube

How to set a DATE SLICER to TODAY by default in Power BI - YouTube

 

The problem is that at present slicers are not able to change selected item dynamically, so all these workarounds are using a specific string like "Default", "Today" to replace the default selected date in a column, and use this column in the slicer to replace the original date column. 

 

Regards,

Jing

Anonymous
Not applicable

Thank You for Reply

I tried "Today" calculated column in slicer but it is in text format ,but all my measures are used "day" which is in integer so it causing error.

 

Hi @Anonymous 

 

You could use "Today" column in slicer, but use original date columns in your measures.

 

Regards,

Jing

Hi @v-jingzhang 

It getting wrong values by doing that 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!