How to send subscribed reports with updated date selections?

[mbc]

Hi,

Im looking for a solution to make our reports select the latest month, whenever it is automatically sent using subscription.

We are using Azure SQL database and so the data updates accordingly, but we need the date selection to follow. We are using Power BI Pro license.

Currently the subscribed reports .png attachment look like this:

But we need it to send a subscribed report with the present month and so forth:

Hoping to find a solution.

[V-pazhen-msft · Accepted solution]

@mbc 

The workaround is set the default slicer to the updated date, please refer to this blog:

 https://littledumbo.com/2019/05/23/how-to-set-default-value-for-slicers-in-power-bi-using-dax/ 

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

[mbc]

Ok, i read the blog. This is a solution for default date picking. Not what we are looking for. 

[GilbertQ]

Hi there

What you will need to do is to have a static filter which will change the months as it moves through the year.

Have a look at this blog post which should be able to explain and get you always showing the latest month: https://www.fourmoo.com/2017/02/15/power-bi-getting-your-report-to-always-filter-this-months-data-dy...

[mbc]

This is interesting. But not column creation is not supported in Power Query, as we read all data from our SQL database. 
What would you surgest we do?

[Sumanth_23]

hi @mbc - can you please elaborate on the point that column creation is not supported in Power Query? You can always fetch your data from SQL and then create a column in Power Query as seen below

There is no option in Power BI to select a default value in a particular slicer which dynamically updates as the data refreshes; the option available is to use a default string (e.g. "Current Month") which is a calculated column in your data model which updates based on a date check. Hence when the month changes from September to October all records for October will be updated in the calculated column to have a value "Current Month". Additionally this calculated column will then have to be used in the slicer instead of the month column for this functionality to work. 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[mbc]

Hi Sumanth,

Sorry, i meant Direct Query - not Power Query.

In Direct Query we are not able to make new Columns.

Im struckeling abit understanding how it can help provide the latest month, week or day. Can you elaborate...?

[Sumanth_23]

hi @mbc  - Ah ok, if you are getting your data using Direct Query this maybe a challenge to implement. 

The approach I was mentioning is highlighted below: 

1. Create a calculated column to calculate the current month

Current Month = IF ( 'Calendar'[Month Number] = MONTH(TODAY()), "Current Month", 'Calendar'[Month Name] )

You can then publish the reports with the filter selected as "Current Month" - when the month switches to October all records for October will be be updated value of "Current Month" and hence the reports will automatically refreshed to display the active month. 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks! 

[mbc]

This is a great reply!
Yeah, its a challange with Direct Query limitations. 

I'll go forward with the solution u describe with my collegue tomorrow.
U'll hear from me again,

Thx again.

[Sumanth_23]

hi @mbc - Sure, give it a go and let me know if it works out. Thanks!