Forum Discussion

jaktony's avatar
jaktony
Helper I
10 months ago
Solved

Dynamic Subscription with a dynamic date based on schedule refresh

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

Data Protection
Need Help
  • v-echaithra's avatar
    v-echaithra
    10 months ago

    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

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    10 months ago

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

  • jaktony's avatar
    jaktony
    Helper I
    10 months ago

    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's avatar
      lbendlin
      Super User
      10 months ago

      Remove the timeline slicer and use the Filter Pane instead. 

      • jaktony's avatar
        jaktony
        Helper I
        10 months ago

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

  • v-echaithra's avatar
    v-echaithra
    Community Support
    10 months ago

    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's avatar
      jaktony
      Helper I
      10 months ago

      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's avatar
        v-echaithra
        Community Support
        10 months ago

        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