comparing old and new datasets

[Anonymous]

Hi everyone, long story and question here 😅

I have just started working with Power BI and was asked to draw up a dashboard based on an Excel projectportfolio dataset. Making the dashboard of the current dataset is not a problem. Another question was to compare the data to historical data. The dataset is however completely restructured this month and so comparing it to previous months is not desired/possible. But, in the future we would like to compare it to previous months. So take Octobre (2021) for t=0, Novembre t=1 and so on. The portfolio dataset is quite big and has 5 KPI's/phases and some additional info that is shown on the dashboard. All KPI's work with RAG classification(Red amber green), and  they count the number of projects in each Phase (or KPI phase) and devide them into either red, amber or green (total 120 projects divided over 5 phases). So it is not sales data, with every change or transaction having a timestamp. Projects just move from phase to phase over time, which is changed in excel (from start, to end). After each month we want to capture the status of that month and see how many projects were in which phase and what colour did they have. I am looking for a way to visualize the trends in every KPI. However defining measures and appending the queries over time is not a sustainable solution and takes a lot of work. I'm asked to set it up now, but I'll leave this team in december, so hopefully i'll find a way to do this semi automatically, or provide a pre-build in solution for my colleagues to work with when I'm gone (they're not really looking for a lot of work on this).

So the question: how to compare current and historical datasets, without the need to manually append these every time and write measures every month?

If it is not possible, it is not possible but every tip is welcome!

Thank you kindly

Gunnar

[lbendlin]

This is indeed a very deep topic.  At some point you will have to decide if you want to stick with the old timey snapshot paradigm, or if you want to implement a more modern event based reporting,

Monthly snapshots may take up a lot of space needlessly,  they are blind to all the drama that may happen to your projects in between snapshots, and you will not be able to recreate them once they have passed. But - they are easier to report against. 

Event based reports are efficient. They use far less storage yet can reproduce any moment in time. But - Power BI is not currently able to do proper time series reporting based on events - that's something you still have to implement manually, outside of Power BI.

[Anonymous]

Thank you for your answer. It is as I had figured indeed. Then we'll just do with the old fashioned snapshot for now! 

[v-shex-msft]

Hi @Anonymous,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng