Store Measures Somewhere for Historical Analysis

[Anonymous]

Hi, I'm now facing a scenario like this:
0. My Tabular model is running in Analylsis Service and My PowerBI is connecting live to the model right now.

1. I have a SCD type 1 Product table

2. I have a measure to calculate TotalSales

3. I have a table visual in my report like below at period 1

ProductCategory       TotalSales

A                                      15

B                                      20

C                                      20

Now let's say my model is refreshed at some point and enter period 2, my table will look like this:

Product Category       TotalSales

A                                      25

B                                      10

C                                      30

I want to run a camparison analysis on the TotalSales of productCategory A between current period and last period, i.e. 25 vs 15. Given that time intelligence function would not work in Period 2 to get number for Period 1 as Product table is a SCD type 1 and category information in Period 1 is lost already.

A very naive way is to copy the table with data and store it somewhere else like in Excel, and do the comparison there. However, it's all manual and not very efficient. Is there any other way to solve the problem?

I found a very similar ask at

https://community.powerbi.com/t5/Desktop/Measure-output-need-to-store-on-new-table/td-p/639087

and I don't think a solution is provided yet.

It will be very much appreciated if anyone can help me with this.

Thanks

[amitchandak]

@Anonymous , refer if this can help

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[edhans]

Power BI is a read-only tool, so it cannot store snapshots for you to do comparisons. You either need to work with your DBA to provide those snapshots, or do the Excel workaround you considered.

[Anonymous]

Thanks for your quick answer. That's what I understand as well.

Currently I was suggested to create two dataflows in PBI Services and both connect to and import the Tabular Model from Analysis Service. One is to automatically refresh the data once the Tabular model is processed and the other one will be refreshed manually, and then create another PBI report using these two Dataflows as source and create a report.

I'm not even sure if this is doable and do you have any idea on this approach? Thanks

[edhans · Accepted solution]

You can definitely use the DataFlow approach. Stagger the refreshes, or refresh manually as you are doing.

You can also use this incremental refresh hack just published today, but it requires a SQL Server to link the data to (or any foldable data source) and not sure how it would work with SSAS. The example is using a CSV file.