Forum Discussion

NewPBI_user's avatar
NewPBI_user
Frequent Visitor
2 years ago

Working with historical data, measure reference

Hello,

 

would you please advice how to efectively work with historical data. I know Power BI does not keep historical data but I am able to have a snapshot of data state from our database. I am using snapshot from the last day of the month because I need to know state of orders. This data set I put into the Power BI as an Excel file. For each month one file.

 

Now I have a matrix table filtered by calendar date and I would need to create a measure to see state of order at the end of last month and at the end of current month. So when I choose January from the slicer, I need to see number of orders from December, and from the end of January. How can I do this? I thought I could reference to relevant table named by the requested date but I dont think it is possible. 

 

Thanks!

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You would use measures that translate your data model connection (calendar table date  to fact table snapshot date) into your expected number ("current"  snapshot plus previous snapshot, for example).  

     

    The best way to deal with snapshots is to not have them. Instead, use a SCD (slowly changing dimension) approach or (best option) a separate change tracker table.

     

    Snapshots are subject to Nyquist/Shannon,  and carry a high risk of standing between you and your insights - especially when the data is very lively in between snapshots. They carry a high storage cost and are also very easy to lose and impossible to recover.