Forum Discussion
Helper IIMovements table to graph by date
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?
4 Replies
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".
TBendersThank you both very much! Solution works perfectly and is very elegant!
