Hi,
I want to create a burndown of tasks that are coming in. They are continually created and completed (not all upfront).
The task table is formatted with each row containing the key info for each task as below:
| Task ID | Creation Date | Last Changed Date | Target Close Date | Open/Closed |
Task ID: Unique identifier
Creation Date: Date task created
Last Changed Date: Date the task was last edited - if the task is closed, then the last changed date is the completion date
Target close date: Date we aim to complete the task based on other factors
Open/Close: Whether the task is still in progress or not
The date dimension table is a standard table, but I can only link it to the Task table on one set of the dates, it is currently linked on the target close date.
In the below, the general 'date' I am referencing is simply the point in the X-axis as this will show the date, and the y axis the count.
For the planned measure:
Count of task IDs for which the creation date is before the date on x-axis, and the target close date is in the future.
For the actual measure:
Count of task IDs for which the creation date and last changed date are before the date on x-axis, and the task status is closed.
The graph needs to be dynamic as the tasks are linked to different teams which users will filter on, so I need to create measures. However, I am struggling to create these with calculate(xyz, filter()) as it won't let me filter on unlinked dates.
Would really appreciate any help you can offer!
