How to capture monthly snapshot data

[Harry_Tran]

Hi everyone,

I have a report which connects to SQL Server and my data is updated daily.

I need to refresh the report monthly on the 2nd day of the month. the logic for the report is:

Report date          Sale date begin       Sale Date End          Post date is equal to or less than

Jan 2 2021                 Aug 2019                 Jul 2020                 12/31/2020

Feb 2 2021                Sep 2019                 Aug 2020               1/31/2021

Mar 2 2021               Oct 2019                  Sep 2020                2/28/2021

Apr 2 2021                Nov 2019                 Oct 202o                3/31/2021   

What I need to do is capture the snapshot data which is refresh every 2nd day of the month. It does not matter how my data is changing I would see the same data for the whole month. Then on the 2nd day it would refresh and add 1 more month of the data and still keep the data of last month. so that I can have the report slicer to choose the specific report date that I need to see the data.

Thank you

[V-lianl-msft]

Hi @Harry_Tran ,

Based on your description, configuring incremental refresh for the dataset can meet your needs. Please refer to:

https://powerbiexperience.com/en/how-to-correctly-use-incremental-refresh-and-not-fall-into-traps/ 

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

[amitchandak]

@Harry_Tran , Based on what I got.

See if if DAX append method can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/