Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Store Measures Somewhere for Historical Analysis

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

 

 

Need Help

4 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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