History of item status

[DaanBakker]

Hi,

I'm making a Power BI visualization for the status of MS Dynamics projects. For example, the status of a project can be changed from 'proposal' in january to 'won' in May and then to 'closed' in June --> this will be new records which I can get in Power BI, looking like

Date - Status - Budget

But my challenge is to get a good visualization of the history of that project. So I want to see what the status and budget of the project was in February, but I don't have that exact date in my data. Is there any way to get that information with my current data?

Thank you!

[MattAllington]

I assume your transactions are days.  So you could create a calendar table of all possible days in your transaction horizon, and then use columns in the calendar table to create he filters for months etc. I have a blog about calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/

assuming your budget is a running total, you will need a measure like closingbalance or using lastdate

http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

as for visualisations, different people like different things. Take a look at the visuals gallery as well as the built in items. 

https://app.powerbi.com/visuals/

the gap analysis might be good if you are reporting against a target. 

I recommend creating a matrix or bar chart first, then write your measures, then work out the best way to visualise.