Month-end Dataflow Refresh Process

[MilindG]

Good morning, community,

I need some guidance on improving our month-end refresh process.

At the moment, we use dataflows for our reports with an incremental refresh strategy (last 10 days), as we retain 2 years' worth of data. Once the financial close occurs, we want to adjust the refresh one time so that it runs only for the previous month, instead of performing the incremental refresh for the full 2-year period.

Any suggestions or best practices you could share would be greatly appreciated!

Thanks in advance.

[MilindG]

@v-heq-msft @KNP 

Sorry for the delayed response.

I do want to still keep the 10 days IR on my dataflow but also wanting to add one time monthly refresh when the accounting closes at 12th of every month.

Unfortunetly I do not have "Last Updated Date" in my DB. 

[KNP · Accepted solution]

There's no way to do what you're wanting while keeping the existing IR policy.

Once the accounting period closes, I'm assuming those "closed" records no longer change.

I would look at splitting the Dataflow into two, one for closed and one for current. You can then have separate IR policies on each and just combine the Dataflows in your model.

[v-heq-msft]

Hi @MilindG ,
Thanks for KNP  corrections to my answers. Here is my correction, for those who wish to change to run incremental refreshes only for the last month, you need to make a change to the incremental refresh period, such as keeping only one month's worth of data and refreshing the latest ten days of data. An incremental refresh is essentially partitioning the data and then refreshing it according to the partition, so if you change the refresh logic then the first time will be a full refresh, but after that it will refresh as you need it to. For example, last month's data is approved on the 12th calendar day of the month. You could set the incremental range to one month and run the refresh schedule on the 12th day of the month and select the Only refresh complete periods option. The system would then refresh January's data (the most recent complete monthly period) on February 12.
Using incremental refresh with dataflows - Power Query | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[KNP]

I'm pretty sure the suggestion from @v-heq-msft is not possible with incremental refresh. I'd loved to be proved wrong though.

Do you have a 'last updated' date in your database? (I'm guessing not or you would have used it)

Is it efficient enough that you could change the IR period to last 2 months or last 45 days?

If your IR is in the Dataflows (I wasn't sure from your post), it might be worthwhile looking at splitting posted/completed (whatever terminology you use) from current, then you may be able to change the incremental logic and in your model, do a Table.Combine for the separate Dataflows.

Difficult to be any more definitive with the info provided.

Hope this gives you some options.

[v-heq-msft]

Hi @MilindG ,
Based on your description, you can try the following to toggle the refresh range. First, you can introduce a parameter in the data stream that allows you to toggle between an incremental refresh and a full refresh for the previous month. Use conditional logic in the data stream to check the value of the parameter. If it is set to “End of month”, adjust the refresh logic to process only the previous month's data. Or you can create a dynamic date filter in Power Query that adjusts to the current date. For month-end processing, set the filter to include only the previous month's data.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly