Forum Discussion

Halloween's avatar
Halloween
Frequent Visitor
3 years ago

Dataflow/Incremental Refresh for creating a monthly accumulative table from a snapshot refresh?

Hi

 

Can we use Incremental Refresh to create a table that contains accumulated monthly snapshot records with all the columns and rows?

 

Suppose I have a Fact transaction table like Customer and there are changes on the table:
* New Customer are created and added
* Status of the Customer could be changed every month e.g. Active / Inactive / In Progress, etc.

 

e.g. if we are doing the Snapshot

On 1st Jan there are 500 records on the Customer table

On 1st Feb there are 800 rows/records on the Customer table 

On 1st Mar there are 1200 rows/records on the Customer table

...

I want to create a Dataflow on PowerBI Service and using Incremental Refresh that would accumulatively keep all the records (not only the delta) from table,  when I am doing every monthly refresh.

 

Therefore what I would like to achieve is a resulting table with these number of record growth:

 

On 1st Jan - 12 midnight - I am expecting to have a result table that produces 500 rows (all the records from 1st Jan)
On 1st Feb - 12 midnight - I will run an incremental refresh and will produce 1300 rows (500 records + 800 records )
On 1st Mar - 12 midnight - I will run an incremental refresh and will produce 2500 rows (500 + 800 + 1200)
...

Can I use Incremental Refresh to create this kind of Table?

 

Thanks in advance