Static and Dynamic Data for report

[gauravg3]

Hi,

I have a trend report for incidents reported in last 12 months. The volume per month is >30,000 on an average.

As a result the data size is getting huge.

So, want to check if its possible to keep 11 months of data static, and only keep just last 1 month data as dynamic? 

Thanks

[PaulDBrown]

You can filter the dataset in Power Query to load the last 12 months only

[littlemojopuppy]

Hi @gauravg3 what do you mean "static" and "dynamic"?

Only refresh the last one month?  Look into incremental refresh.

[gauravg3]

Hi, sorry should have explained in a more clear way.

Thing is I am extracting data from Service Now and using power automate to get the data in Power BI (via Outlook). Since the data size is getting higher, data is not getting attached to outlook.

As the count for last 11 months would remain unchanged, so was thinking if within Power BI , is there a way to add those values as numbers (may be in table, or something similar) and only apply the query to get the data for last 1 month; this way the data size would reduce to only 1 month data. 

[v-rzhou-msft · Accepted solution]

Hi @gauravg3 ,

According to your statement, I know your data source is Servicenow. It is a web data source.  Incremental refresh is a good way to load data in dynamic date range. However web data source doesn't support query folding, so incremental refresh may show bad performance due to large size of data.

For reference: Sources that support folding

Here I suggest you to do some transform in Power Query Editor.

Filter static table in the previous 11 months.

Then use Group BY to get aggregate values.

Filter dynamic table by This Month.

You may refer to this blog to learn more details about how to optmize your report performance.

Best Regards,
Rico Zhou

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