Dynamic Subscription with a dynamic date based on schedule refresh

[jaktony]

Hi All,

I'm trying to create a dynamic subscription that would change the date field in the report based on the scheduled subscription.

For example, if I have a monthly scheduled subscription for a report sent out in October - I would want the data in my report to only show october data.

Is this possible?

Thanks

[v-echaithra]

Hi @jaktony ,

Thank you @lbendlin  for your inputs.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

[jaktony]

Hi @v-echaithra ,

Unfortantely, I wasn't able to resolve the issue. I'm not sure whether I'm not doing a good job explaining the issue or whether I'm not understanding the response to it.

Thanks

[v-echaithra · Accepted solution]

Hi @jaktony ,

Unfortunately, Power BI does not directly allow dynamic slicer values in subscriptions i.e., you can't have the slicer itself change dynamically based on the subscription schedule. However, there are a few workarounds to achieve similar functionality:

Use a DAX Measure for Dynamic Filtering Based on Current Month

Create a Dynamic Measure for the Month Filter:
You can write a DAX measure to calculate the data for the current month or the month of the subscription:

CurrentMonthData =
CALCULATE (
[YourMeasure],
YEAR('Date'[Date]) = YEAR(TODAY()) && MONTH('Date'[Date]) = MONTH(TODAY()))

Instead of using a slicer, you can apply this measure to your visuals to show data only for the current month. This way, when the report is generated in October, it will automatically filter to show only October data.
The subscription will run according to the scheduled time e.g., the first of the month. Since the DAX measure dynamically adjusts to the current month based on the system date (TODAY()), it will always reflect the month corresponding to the subscription schedule.

Hope this helps.
Chaithra E

[jaktony]

Thank you! This is super helpful

[v-echaithra]

HI @jaktony ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[jaktony]

@v-echaithra yup, this solution resolved my issue. Thanks!

[jaktony]

@lbendlin  Thank you for the reply. 

I don't fully understand but let me give you more information regarding my dashboard.

My report page is run based on a timeline slicer (see image below). I want to be to dynamically have the schdueled month correspond to the timeline slicer. So that user's for the Oct subscription will see Oct related data. Is this possible or there a workaround?

Thanks

[lbendlin]

Remove the timeline slicer and use the Filter Pane instead. 

[jaktony]

If I remove the slicer and use the filter pane instead, how would I set up the dynamic date filter to the monthly subscription?

[lbendlin]

[lbendlin]

Assuming that your semantic model is refreshed (or in Direct Query mode) you get this for free if you use the filter pane with Relative date filtering. Specify "in currrent month" as a filter.  The subscription will rerender EVERY SINGLE PAGE of your report for EVERY SINGLE RECIPIENT, and will pick up that filter.

(sorry for shouting, but subscriptions are a nightmare for that reason).