cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaanBakker Occasional Visitor
Occasional Visitor

History of item status

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!

1 REPLY 1
Highlighted
Super User
Super User

Re: History of item status

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.