dataflow Incremental Refersh Q

[smpa01]

I have a datasource which is as following

| Year | Period | firstDay | Finperiod | tranDateTime      | doc#   | value |
|------|--------|----------|-----------|-------------------|--------|-------|
| 2016 | 1      | 1        | 201601    | 1/1/16 12:00 AM   | doc1   | 2896  |
| 2016 | 1      | 1        | 201601    | 1/31/16 12:00 AM  | doc2   | 1770  |
| 2016 | 2      | 1        | 201602    | 2/1/16 12:00 AM   | doc3   | 2096  |
| 2016 | 2      | 1        | 201602    | 2/29/16 12:00 AM  | doc4   | 1158  |
| 2016 | 3      | 1        | 201603    | 3/1/16 12:00 AM   | doc5   | 1544  |
| 2016 | 3      | 1        | 201603    | 3/31/16 12:00 AM  | doc6   | 1438  |
| 2016 | 4      | 1        | 201604    | 4/1/16 12:00 AM   | doc7   | 3378  |
| 2016 | 4      | 1        | 201604    | 4/30/16 12:00 AM  | doc8   | 3813  |
| 2016 | 5      | 1        | 201605    | 5/1/16 12:00 AM   | doc9   | 1586  |
| 2016 | 5      | 1        | 201605    | 5/31/16 12:00 AM  | doc10  | 1086  |
| 2016 | 6      | 1        | 201606    | 6/1/16 12:00 AM   | doc11  | 3091  |
| 2016 | 6      | 1        | 201606    | 6/30/16 12:00 AM  | doc12  | 4176  |
| 2016 | 7      | 1        | 201607    | 7/1/16 12:00 AM   | doc13  | 2073  |
| 2016 | 7      | 1        | 201607    | 7/31/16 12:00 AM  | doc14  | 3329  |
| 2016 | 8      | 1        | 201608    | 8/1/16 12:00 AM   | doc15  | 4096  |
| 2016 | 8      | 1        | 201608    | 8/31/16 12:00 AM  | doc16  | 4067  |
| 2016 | 9      | 1        | 201609    | 9/1/16 12:00 AM   | doc17  | 4585  |
| 2016 | 9      | 1        | 201609    | 9/30/16 12:00 AM  | doc18  | 2454  |
| 2016 | 10     | 1        | 201610    | 10/1/16 12:00 AM  | doc19  | 4306  |
| 2016 | 10     | 1        | 201610    | 10/31/16 12:00 AM | doc20  | 1703  |
| 2016 | 11     | 1        | 201611    | 11/1/16 12:00 AM  | doc21  | 4405  |
| 2016 | 11     | 1        | 201611    | 11/30/16 12:00 AM | doc22  | 1587  |
| 2016 | 12     | 1        | 201612    | 12/1/16 12:00 AM  | doc23  | 1710  |
| 2016 | 12     | 1        | 201612    | 12/31/16 12:00 AM | doc24  | 4671  |
| 2017 | 1      | 1        | 201701    | 1/1/17 12:00 AM   | doc25  | 4791  |
| 2017 | 1      | 1        | 201701    | 1/31/17 12:00 AM  | doc26  | 1260  |
| 2017 | 2      | 1        | 201702    | 2/1/17 12:00 AM   | doc27  | 1135  |
| 2017 | 2      | 1        | 201702    | 2/28/17 12:00 AM  | doc28  | 3117  |
| 2017 | 3      | 1        | 201703    | 3/1/17 12:00 AM   | doc29  | 1329  |
| 2017 | 3      | 1        | 201703    | 3/31/17 12:00 AM  | doc30  | 4561  |
| 2017 | 4      | 1        | 201704    | 4/1/17 12:00 AM   | doc31  | 3586  |
| 2017 | 4      | 1        | 201704    | 4/30/17 12:00 AM  | doc32  | 4529  |
| 2017 | 5      | 1        | 201705    | 5/1/17 12:00 AM   | doc33  | 3027  |
| 2017 | 5      | 1        | 201705    | 5/31/17 12:00 AM  | doc34  | 1855  |
| 2017 | 6      | 1        | 201706    | 6/1/17 12:00 AM   | doc35  | 4334  |
| 2017 | 6      | 1        | 201706    | 6/30/17 12:00 AM  | doc36  | 1431  |
| 2017 | 7      | 1        | 201707    | 7/1/17 12:00 AM   | doc37  | 2279  |
| 2017 | 7      | 1        | 201707    | 7/31/17 12:00 AM  | doc38  | 4316  |
| 2017 | 8      | 1        | 201708    | 8/1/17 12:00 AM   | doc39  | 2558  |
| 2017 | 8      | 1        | 201708    | 8/31/17 12:00 AM  | doc40  | 1874  |
| 2017 | 9      | 1        | 201709    | 9/1/17 12:00 AM   | doc41  | 4812  |
| 2017 | 9      | 1        | 201709    | 9/30/17 12:00 AM  | doc42  | 2935  |
| 2017 | 10     | 1        | 201710    | 10/1/17 12:00 AM  | doc43  | 4037  |
| 2017 | 10     | 1        | 201710    | 10/31/17 12:00 AM | doc44  | 4460  |
| 2017 | 11     | 1        | 201711    | 11/1/17 12:00 AM  | doc45  | 3760  |
| 2017 | 11     | 1        | 201711    | 11/30/17 12:00 AM | doc46  | 2409  |
| 2017 | 12     | 1        | 201712    | 12/1/17 12:00 AM  | doc47  | 3846  |
| 2017 | 12     | 1        | 201712    | 12/31/17 12:00 AM | doc48  | 2216  |
| 2018 | 1      | 1        | 201801    | 1/1/18 12:00 AM   | doc49  | 1534  |
| 2018 | 1      | 1        | 201801    | 1/31/18 12:00 AM  | doc50  | 4996  |
| 2018 | 2      | 1        | 201802    | 2/1/18 12:00 AM   | doc51  | 2184  |
| 2018 | 2      | 1        | 201802    | 2/28/18 12:00 AM  | doc52  | 1610  |
| 2018 | 3      | 1        | 201803    | 3/1/18 12:00 AM   | doc53  | 1975  |
| 2018 | 3      | 1        | 201803    | 3/31/18 12:00 AM  | doc54  | 4026  |
| 2018 | 4      | 1        | 201804    | 4/1/18 12:00 AM   | doc55  | 3038  |
| 2018 | 4      | 1        | 201804    | 4/30/18 12:00 AM  | doc56  | 2325  |
| 2018 | 5      | 1        | 201805    | 5/1/18 12:00 AM   | doc57  | 3738  |
| 2018 | 5      | 1        | 201805    | 5/31/18 12:00 AM  | doc58  | 3026  |
| 2018 | 6      | 1        | 201806    | 6/1/18 12:00 AM   | doc59  | 2559  |
| 2018 | 6      | 1        | 201806    | 6/30/18 12:00 AM  | doc60  | 2982  |
| 2018 | 7      | 1        | 201807    | 7/1/18 12:00 AM   | doc61  | 2006  |
| 2018 | 7      | 1        | 201807    | 7/31/18 12:00 AM  | doc62  | 1337  |
| 2018 | 8      | 1        | 201808    | 8/1/18 12:00 AM   | doc63  | 3184  |
| 2018 | 8      | 1        | 201808    | 8/31/18 12:00 AM  | doc64  | 4203  |
| 2018 | 9      | 1        | 201809    | 9/1/18 12:00 AM   | doc65  | 1032  |
| 2018 | 9      | 1        | 201809    | 9/30/18 12:00 AM  | doc66  | 4486  |
| 2018 | 10     | 1        | 201810    | 10/1/18 12:00 AM  | doc67  | 4094  |
| 2018 | 10     | 1        | 201810    | 10/31/18 12:00 AM | doc68  | 3977  |
| 2018 | 11     | 1        | 201811    | 11/1/18 12:00 AM  | doc69  | 1760  |
| 2018 | 11     | 1        | 201811    | 11/30/18 12:00 AM | doc70  | 1108  |
| 2018 | 12     | 1        | 201812    | 12/1/18 12:00 AM  | doc71  | 2600  |
| 2018 | 12     | 1        | 201812    | 12/31/18 12:00 AM | doc72  | 3117  |
| 2019 | 1      | 1        | 201901    | 1/1/19 12:00 AM   | doc73  | 3381  |
| 2019 | 1      | 1        | 201901    | 1/31/19 12:00 AM  | doc74  | 2496  |
| 2019 | 2      | 1        | 201902    | 2/1/19 12:00 AM   | doc75  | 3102  |
| 2019 | 2      | 1        | 201902    | 2/28/19 12:00 AM  | doc76  | 2990  |
| 2019 | 3      | 1        | 201903    | 3/1/19 12:00 AM   | doc77  | 3129  |
| 2019 | 3      | 1        | 201903    | 3/31/19 12:00 AM  | doc78  | 4906  |
| 2019 | 4      | 1        | 201904    | 4/1/19 12:00 AM   | doc79  | 1483  |
| 2019 | 4      | 1        | 201904    | 4/30/19 12:00 AM  | doc80  | 2618  |
| 2019 | 5      | 1        | 201905    | 5/1/19 12:00 AM   | doc81  | 4245  |
| 2019 | 5      | 1        | 201905    | 5/31/19 12:00 AM  | doc82  | 2005  |
| 2019 | 6      | 1        | 201906    | 6/1/19 12:00 AM   | doc83  | 2165  |
| 2019 | 6      | 1        | 201906    | 6/30/19 12:00 AM  | doc84  | 4257  |
| 2019 | 7      | 1        | 201907    | 7/1/19 12:00 AM   | doc85  | 2106  |
| 2019 | 7      | 1        | 201907    | 7/31/19 12:00 AM  | doc86  | 1460  |
| 2019 | 8      | 1        | 201908    | 8/1/19 12:00 AM   | doc87  | 2386  |
| 2019 | 8      | 1        | 201908    | 8/31/19 12:00 AM  | doc88  | 4407  |
| 2019 | 9      | 1        | 201909    | 9/1/19 12:00 AM   | doc89  | 4652  |
| 2019 | 9      | 1        | 201909    | 9/30/19 12:00 AM  | doc90  | 4113  |
| 2019 | 10     | 1        | 201910    | 10/1/19 12:00 AM  | doc91  | 2958  |
| 2019 | 10     | 1        | 201910    | 10/31/19 12:00 AM | doc92  | 1693  |
| 2019 | 11     | 1        | 201911    | 11/1/19 12:00 AM  | doc93  | 2184  |
| 2019 | 11     | 1        | 201911    | 11/30/19 12:00 AM | doc94  | 2200  |
| 2019 | 12     | 1        | 201912    | 12/1/19 12:00 AM  | doc95  | 3856  |
| 2019 | 12     | 1        | 201912    | 12/31/19 12:00 AM | doc96  | 1669  |
| 2020 | 1      | 1        | 202001    | 1/1/20 12:00 AM   | doc97  | 4604  |
| 2020 | 1      | 1        | 202001    | 1/31/20 12:00 AM  | doc98  | 4628  |
| 2020 | 2      | 1        | 202002    | 2/1/20 12:00 AM   | doc99  | 1768  |
| 2020 | 2      | 1        | 202002    | 2/29/20 12:00 AM  | doc100 | 1306  |
| 2020 | 3      | 1        | 202003    | 3/1/20 12:00 AM   | doc101 | 1267  |
| 2020 | 3      | 1        | 202003    | 3/31/20 12:00 AM  | doc102 | 3435  |
| 2020 | 4      | 1        | 202004    | 4/1/20 12:00 AM   | doc103 | 4188  |
| 2020 | 4      | 1        | 202004    | 4/30/20 12:00 AM  | doc104 | 4515  |
| 2020 | 5      | 1        | 202005    | 5/1/20 12:00 AM   | doc105 | 2561  |
| 2020 | 5      | 1        | 202005    | 5/31/20 12:00 AM  | doc106 | 3979  |
| 2020 | 6      | 1        | 202006    | 6/1/20 12:00 AM   | doc107 | 3160  |
| 2020 | 6      | 1        | 202006    | 6/30/20 12:00 AM  | doc108 | 2025  |
| 2020 | 7      | 1        | 202007    | 7/1/20 12:00 AM   | doc109 | 3158  |
| 2020 | 7      | 1        | 202007    | 7/31/20 12:00 AM  | doc110 | 1422  |
| 2020 | 8      | 1        | 202008    | 8/1/20 12:00 AM   | doc111 | 1808  |
| 2020 | 8      | 1        | 202008    | 8/31/20 12:00 AM  | doc112 | 2223  |
| 2020 | 9      | 1        | 202009    | 9/1/20 12:00 AM   | doc113 | 2683  |
| 2020 | 9      | 1        | 202009    | 9/30/20 12:00 AM  | doc114 | 3874  |
| 2020 | 10     | 1        | 202010    | 10/1/20 12:00 AM  | doc115 | 1144  |
| 2020 | 10     | 1        | 202010    | 10/31/20 12:00 AM | doc116 | 3016  |
| 2020 | 11     | 1        | 202011    | 11/1/20 12:00 AM  | doc117 | 2262  |
| 2020 | 11     | 1        | 202011    | 11/30/20 12:00 AM | doc118 | 3201  |
| 2020 | 12     | 1        | 202012    | 12/1/20 12:00 AM  | doc119 | 1777  |
| 2020 | 12     | 1        | 202012    | 12/31/20 12:00 AM | doc120 | 4448  |
| 2021 | 1      | 1        | 202101    | 1/1/21 12:00 AM   | doc121 | 4585  |
| 2021 | 1      | 1        | 202101    | 1/31/21 12:00 AM  | doc122 | 1109  |
| 2021 | 2      | 1        | 202102    | 2/1/21 12:00 AM   | doc123 | 2596  |
| 2021 | 2      | 1        | 202102    | 2/28/21 12:00 AM  | doc124 | 1881  |
| 2021 | 3      | 1        | 202103    | 3/1/21 12:00 AM   | doc125 | 4117  |
| 2021 | 3      | 1        | 202103    | 3/31/21 12:00 AM  | doc126 | 4853  |
| 2021 | 4      | 1        | 202104    | 4/1/21 12:00 AM   | doc127 | 1387  |
| 2021 | 4      | 1        | 202104    | 4/30/21 12:00 AM  | doc128 | 1594  |
| 2021 | 5      | 1        | 202105    | 5/1/21 12:00 AM   | doc129 | 4519  |
| 2021 | 5      | 1        | 202105    | 5/31/21 12:00 AM  | doc130 | 1177  |
| 2021 | 6      | 1        | 202106    | 6/1/21 12:00 AM   | doc131 | 2815  |
| 2021 | 6      | 1        | 202106    | 6/30/21 12:00 AM  | doc132 | 1532  |
| 2021 | 7      | 1        | 202107    | 7/1/21 12:00 AM   | doc133 | 1366  |
| 2021 | 7      | 1        | 202107    | 7/31/21 12:00 AM  | doc134 | 4807  |
| 2021 | 8      | 1        | 202108    | 8/1/21 12:00 AM   | doc135 | 1741  |
| 2021 | 8      | 1        | 202108    | 8/31/21 12:00 AM  | doc136 | 3737  |
| 2021 | 9      | 1        | 202109    | 9/1/21 12:00 AM   | doc137 | 4642  |
| 2021 | 9      | 1        | 202109    | 9/30/21 12:00 AM  | doc138 | 2922  |
| 2021 | 10     | 1        | 202110    | 10/1/21 12:00 AM  | doc139 | 4268  |
| 2021 | 10     | 1        | 202110    | 10/31/21 12:00 AM | doc140 | 1812  |
| 2021 | 11     | 1        | 202111    | 11/1/21 12:00 AM  | doc141 | 4066  |
| 2021 | 11     | 1        | 202111    | 11/30/21 12:00 AM | doc142 | 4430  |
| 2021 | 12     | 1        | 202112    | 12/1/21 12:00 AM  | doc143 | 2476  |
| 2021 | 12     | 1        | 202112    | 12/31/21 12:00 AM | doc144 | 33001 |
| 2021 | 12     | 1        | 202112    | 12/31/21 12:01 AM | doc144 | 3300  |
| 2022 | 1      | 1        | 202201    | 1/1/22 12:00 AM   | doc197 | 5000  |

I have created a dataflow with this and set up an incremental refresh as following

Incremental Refrsh Setting

I query this df in other power bi datasets/reports.

However, with this incremental refresh setting on this df, when I query the df in subsequent pbi dataset, I don't see the last row of 2022 at all, where as if I completely switch off the incremetal refresh I see all the rows.

I am not sure why it behaves this way ?

[Fowmy]

@smpa01 

When you set "Refresh Rows from the past" to 1 year, it refreshes up to the current date but you also mentioned as "last row of 2022". It has to be on or before the current date.

A good reference: https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

[smpa01]

Thanks @Fowmy for looking into this and helping me decipher inceremental refresh.

I set up "Refresh rows from the Past 1 year" with the hope

that

     Incremental will bring all the rows but update only the rows from the past 1 year

and

     I thought that based on the DATETIME column (in this case tranDateTime)

     Power BI will figure out what the lastest Year is (2022 in this case)

            and

                  Refresh rows from past 1 year (Refresh rows from 1 year past of 2022)

But that is clearly not the case and How should I interpret what Power BI interprets as - Refresh Rows from the past* ____ Years

It can't be current calendar date as my query returns everything from the df upto trandDateTime 12/31/2021 12:01:00 AM and today is 06/30/2021. If Incremental refresh can go past today, why it stops at 12/31/2021 12:01:00 AM?

[Fowmy]

@smpa01 

I need to do an experiment and get back to you, meanwhile, others will definitely help you out.

[smpa01]

I have done several experiments with incremental refresh on df today and I can confirm that if my dataset contains rows with future dateTime value [2022-01-01 12:00:00 AM which is > last day of the current year (2021-12-31 11:59:59 PM)], the refresh will not return those rows after incremental refresh when set up with Refresh rows from past 1 year. 

I dont know whether it is a bug or meant to be like that, would be nice if someone from MS can confirm that.

I am trying to determine if I should set up incremental refresh on a df which contains transaction dataset that runs on a custom financial calendar where the period runs from 16th-15th (e.g. 2021period1 runs from 12-16-2020 12:00:00 AM to 01-15-2021 11:59;59 PM). I dont want to run into a situation on 16th dec on a given calendar year with this current incremental refresh setting where it does not bring data from the newP1 financial year. If that is going to be the case, I dont want to rely on incremental.