Forum Discussion
Movements table to graph by date
- Anonymous9 years ago
Oh hey, we even tackled blank end dates in that thread. There you go.
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".