Power Automate flow to copy batch from previous month to current month in Data Verse

[Anonymous]

Hi, I need a few ideas for this task I have to do if anyone can please help: 

I have a table in Dataverse called Forecasts and a few of the columns are: SnapshotMonth, CurrentPeriod, Quantity etc. 

What I need to do is: create a power automate flow where at the beginning of each month, a new snapshot is created, meaning: all data from the previous snapshot month will be copied to the new snapshot month and the column CurrentPeriod will change to "No" for the previous snapshot month for all values. 

I haven't done much in Power automate and I am having a hard time figuring this out. 

What I found so far is that I have to list all rows present in the table and somehow filter the ones with the previous snapshot month (which means previous calendar month) and create rows in the table with the filtered values. 

Thanks in advance for your help

[v-wenjuan-msft · Accepted solution]

Hi @Anonymous ,

I make a demo based on your request. However, Power Automate cannot help you create a Dataverse table directly, you may need to create it yourself and change the table ID to the flow every month. Then the flow will handle data from last month table to current table.

Refer to below snap and I will give you an introduction:

1. As you may need to create a new table each month and replace the table ID into the flow action, advice is to trigger the flow manually.

2. Use below functions to get start of current month and start of last month,

startOfMonth(convertFromUtc(utcNow(),'China Standard Time'),'yyyy-MM-dd')

startOfMonth(addDays(outputs('Compose-to_get_start_of_this_month'),-1),'yyyy-MM-dd')

Remember to replace 'China Standard Time' to your time region. Refer to this link:Default Time Zones | Microsoft Learn

3. "List rows", connect to last month table to get data.

4. "Filter array", in my demo, I use below function to filter out records created on last month.

@and(greaterOrEquals(formatdatetime(item()?['createdon'], 'yyyy-MM-dd'), outputs('Compose-to_get_start_of_this_month')), less(formatdatetime(item()?['createdon'], 'yyyy-MM-dd'), outputs('Compose-to_get_start_of_this_month')))

5. "Apply to each", notice that the input we use here should be outputs from "Filter array".

Use below function to call data from your old table. Remember to change column name to your logical column name (find your column in Dataverse table, Edit column-->Advanced options-->Logical name).

items()?['changetoyourcolumnname']

6. "Apply to each 2", Notice that the input here change to outputs from "List rows". Just change CurrentPeriod column to No for all records.

Community Support Team _ Wenjuan Zou

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

[Anonymous]

This is of great help.

It was my mistake I did not mention it, but actually this update happens in the same table, I do not need to create a new table. So basically, I only need to change "CurrentPeriod" to "No" for the previous snapshot month and for the current snapshot month to just copy all the rest of columns and make sure the SnapshotMonth is the current month . I'm sorry I did not specify this clearer earlier.

I did not get the chance to implement this yet but it seems like it is what I need.

I will mark it as a solution if I don't have any issues with this approach.

Thanks a lot for your time!