Movements table to graph by date

[TBenders]

Hi,

I'm hopeful someone here knows how to achieve this:

I want to create a line graph that shows the amount vacancies we had open by date. I have a table that has the opening and closing dates. I need the graph to count each vacancy on dates in between opening and closing(the time it was open) as 1. My idea is to approach this as if it were a stock/inventory movements table.

Example of my table: VacancyMovements 

VacancyID	DateOpen	DateClosed
10001	        4-9-2016	14-9-2016
10002	        5-9-2016	27-9-2016
10003	        14-9-2016	17-9-2016
10004	        14-9-2016	23-9-2016
10005	        17-9-2016	 
10006	        22-9-2016	 

Some vacancies dont have a closed date yet because theyre still open. They should count as one from the open date untill today.

In reality, im working with a table that has about 1000 rows with about 50-100 vacancies actually being open at any given date. 

What I tried:

I made 2 seperate tables: VacanciesOpen and VacanciesClosed. 

Then used 2 measures: RunningOpen and RunningClosed to get a running number of both up to date.

Then do RunningOpen - RunningClosed, resulting in OpenOnDate but this does not seem to get the result I expect...

Anyone have another idea?

[TBenders]

Thank you both very much! Solution works perfectly and is very elegant! 

[KHorseman]

You will need a disconnected date table for this. I'm guessing but not certain that you do have a dedicated date table in your data model. If not it is an easily googlable thing.

Usually with a date table you would create a relationship between it and a date column in your fact table, but that only works when the items you're trying to count can be thought of as an event that happened on one date. Like a sale. But what you're counting can be best thought of as a thing that continued happening over a period of dates, because you want to count while it was open. You have two dates but really there are an indeterminate number of dates between that you also want to include, and you definitely can't create a relationship to any column representing those. So you will create no relationship at all between the date table and the vacancies table, hence a disconnected date table. You only need the VacancyMovements table for this.*

Running Vacancies = CALCULATE(
	DISTINCTCOUNT(VacancyMovements[VacancyID]),
	FILTER(
		VacancyMovements,
		VacancyMovements[DateOpen] <= LASTDATE(DateTable[Date]) &&
		(VacancyMovements[DateClosed] >= FIRSTDATE(DateTable[Date]) ||
		ISBLANK(VacancyMovements[DateClosed]))
	)
)

Then you would plot that against DateTable[Date] or whatever other columns you have like DateTable[Week] or DateTable[Month].

*there is another way that does involve another table and a relationship with the date table, but this is the easier method. Especially since you have some without closing dates. But if you want an alternative method I can get into it. Or you can search around for an old thread on this forum called, I think, "Generate a Schedule Table".

[Sean]

@TBenders also checkout this post

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

@KHorseman explains that formula there in detail

[KHorseman · Accepted solution]

Oh hey, we even tackled blank end dates in that thread. There you go.