Static table for aggregated data(using calculated table)

[Anonymous]

Hi, We have a requirement to create a calculated table for aggregated data. Our business wants rolling 12 months for current data and seperate table(s) to store the aggregared data for calendar year, for historical analysis. Ideally these historical tables should be static as the data is not going to change in them.

We use SSAS(2016) tabular model and our business connect to SSAS tabular using Power BI.

I am creating the rolling 12 month calculated table based on this criteria : 'Table'[ProjectBeginDateKey] >= TODAY() - 365. And this seems to be working as desired.

However, I am not sure on how to create static tables in SSAS (or in Power BI) using DAX. So, I am planning to create the historical table(s) using the same criteria as above i.e. 'Table'[ProjectBeginDateKey] >= "1/1/2018" and 'Table'[ProjectBeginDateKey] >= "12/31/2018". This approach will solve my problem but I feel it has the following issues:

1. This is not efficient as the historical table always be refreshed with the data based on the respective date range. Instead of being static tables these become dynamic tables, which I believe as years pass will become a huge bottle neck on performance.
2. At the beginning of each new year, we have to manually create the historical table. (Note: Even if there is a way to create static table, I still think we need to go through this step manually-every year)

I am looking for some ideas and inputs on how best I can overcome this issue. Please let me know if you need any additioanl details.

PS: If you don't use/know SSAS tabular model, the same problem can be simulated/solved in Power BI as both uses DAX(atleast in my case).

Thanks a bunch

[v-juanli-msft]

Hi

When it comes to the year 2019, you want date for the last 12 months of 2018 (1/1/2018-12/31/2018)

So you input the formula like this in the table,

Table = FILTER(Sheet1,[ProjectBeginDateKey]>=DATE(2018,1,1)&&[ProjectBeginDateKey]<=DATE(2018,12,31))

Is my understanding right?

If it is in this case, it would be a static table, so I don’t understand what do you mean by saying “as the historical table always be refreshed with the data based on the respective date range”.

Best Regards

Maggie

[Anonymous]

Thanks for the response but the solution offered will create a dynamic table, which always refers to the main table. I want to create a copy of it using the main table and let's say in the future if the main table is purged, we should still see the data in this static table.