I have a list of different projects with their planned start/ finish date and actual start/finish data.
Now, I want to show a KPI that displays count of projects ongoing or newly started in a particular month. For example, I build a KPI with title as current month( based on Actual Start/ Finish date) where project name jkl should be counted in the month of March(when project has its actual start date) as well as April( where the project is ongoing). So if a project has a start date of February and end date of April, the project should be counted in each and every month between start and end date. I tried using master calendar measure but it doesn't have the option of showing ongoing months.
Can any one help what is the best way to show such KPI in dash board ?
You can build an Interval Match table that enables you to do this.
I created an inline table of your data and a sample IntervalMatch table. I hard-coded everything, but for a permanent solution, you'd want to calculate the YearMonth fields and create the IntervalMatch table using logic similar to creating a MasterCalendar Table.
The Interval Match does a nice job of linking each project to each month that it occurs in. By removing the "day" part of the date and using just Year and Month, it enables you to ignore when in the month and just that it was in the month.
See the attached Sample.qvf file.
You will need to be careful in how you use the Interval Match data and clean-up should be done to remove the synthetic key, but this should point you in the right direction. There may be other options, but this has worked for me in the past.
Here's my load script and I'll attach a few pictures of a table and stacked bar visualization.
LOAD * Inline [
Project Name,Actual Start,Actual Finish,ActualStartYearMonth,ActualFinishYearMonth
LOAD * Inline [
Inner Join IntervalMatch ( IntervalYearMonth )
LOAD ActualStartYearMonth, ActualFinishYearMonth