Conditional Refresh Based on Date

[PowerUp1]

I'd like to construct a refresh that only executes when the date is more than the 5th day of the month.  The table is a calendar table that automatically moves the current period designations along with the current calendar date.  During that first week is when the analysis of the month occurs.  Freezing the calendar dimensions is needed.

However, the other data tables would need to continue to be refreshed during that first week.

Any ideas?

[Adamboer]

You can achieve this by creating two separate refresh schedules, one for the calendar table and one for the other data tables.

For the calendar table, you can set the refresh schedule to occur only when the date is greater than or equal to the 5th day of the month. You can do this by creating a calculated column in the calendar table that calculates the current day of the month using the DAX function DAY(). You can then set the refresh schedule to occur only when this calculated column is greater than or equal to 5.

For the other data tables, you can set the refresh schedule to occur daily or as frequently as needed, regardless of the date.

To set up these separate refresh schedules, you can use the "Scheduled refresh" feature in Power BI. In the "Scheduled refresh" settings, you can set up a refresh schedule for each table and specify the refresh settings for each table separately.

Here are the steps to set up these separate refresh schedules:

1. Open the "Scheduled refresh" settings for your report or dataset.
2. Click "Add table" to add the calendar table.
3. Set the refresh settings for the calendar table to occur only when the calculated column for the day of the month is greater than or equal to 5.
4. Click "Add table" again to add the other data tables.
5. Set the refresh settings for the other data tables to occur daily or as frequently as needed.

By setting up these separate refresh schedules, you can ensure that the calendar table is only refreshed after the 5th day of the month, while the other data tables continue to be refreshed daily or as needed.

[carinalou]

It's been a year since you posted this, but any chance you found (and rememeber) a solution? I'm facing this exact problem.

[PowerUp1]

I never found a way to do a conditional query.  I did however use a variable in my Power Query to set the reporting period.

  ReportingDate = if Date.Day(DateTime.LocalNow()) > 9 then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),0) else Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-1),

then I filtered my fact data accordingly:

  #"Filtered Rows1" = Table.SelectRows(DateFormat, each [Date] <= ReportingDate),

In my date tables I set a reporting period flag and my relative periods accordingly.

Hope that helps

[carinalou]

Great, thanks so much for the reply!

[v-yingjl]

Hi @PowerUp1 ,

As far as I know, currently 'conditional refresh' would mean incremental refresh to refresh the date range data in power bi.

Please refer this document about incremental refersh: Incremental refresh in Power BI 

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

[PowerUp1]

Hi @Jimmy801 

Thanks for the thoughts.

The data set is a comprehensive date table which provides a number of relative values.  For example, if refreshed on February 28th the compared periods are all relative to February (prior year month, same month prior quarter, etc.).  Once refreshed in March all of those relative periods change.

I don't have control over the date table, therefore looking to 'pause' the refresh for that table only to give a window for analysis.

Your comment about referring to another table might work if I combined it with a M If statement.  Let me play with that.

[Jimmy801]

Hello @PowerUp1 

okay, give it a try and let us know

BR

Jimmy

[Jimmy801]

Hello @PowerUp1 

I don't know anything about conditional refresh in this regard. What exactly is your query containing? Is it applying a filter based on a date or choosing a file according to the date?

Because the only thing you could do is to include in your query a if step that checks the current day and if its >5 then query is running normally and if not refer to a other table, file or apply another filter.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy