Trying to track % complete over time and contrast with past periods in line graph
Hi All, I'm trying to figure out the measures/variables that I need to create a visual that tracks progress over time and contrasts it against previous years. Here's a sample of my data Task Year Task Start Date Task ID Task Status Date Completed Note 2022 Apr-01-2022 1 Active Task uncomplete 2022 Apr-01-2022 2 Complete 05/25/2022 Should count for 2022 2022 Apr-01-2022 3 Complete 07/30/2022 Should count for 2022 2021 Apr-01-2021 1 Complete 04/19/2022 Completed after Mar 31st year-end, should not count for % complete for 2021 or 2022 2021 Apr-01-2021 2 Complete 08/08/2021 Should count for 2021 2021 Apr-01-2021 3 Complete 10/04/2021 Should count for 2021 2020 Apr-01-2020 1 Active Task uncomplete 2020 Apr-01-2020 2 Complete 02/20/2020 Should count for 2020 2020 Apr-01-2020 3 12/15/2022 Completed after Mar 31st 2020 year end, should not count for % complete for 2020 or 2022 Here's an example of what I'm hoping to accomplish- So there are a few things to note- Some tasks are unfinished and do not have a date-completed value. I have tried relating my calendar table to both the Start Date and End Date columns, but both have been problematic. Some tasks are completed in later years, but I only want to track the progress of tasks in the given task year. The calendar year is nonstandard and runs from Apr 1 to Mar 31 I think I need 3 measures/variables: One that counts the total number of tasks assigned for a given task year... TaskCount=Calculate(Countrows(My Table),...Filter or something? One that sums up the number of tasks completed over time for a given year. I've been closest with this one i think, but it doesnt aggregate or sum... Complete = CALCULATE(COUNTROWS(Stats),FILTER(Stats,[Task Status]="Complete")) One that calculates the % of total annual tasks completed as the year progresses.... %Complete= DIVIDE(Complete, TaskCount) The problems I've been having- I was trying different variations of COUNTROWS to get my total task count but was having problems due to the chart's X axis being months. I was also having problems using my date table in this way. I think I need a variable based on a measure that counts the number of tasks for each task year and holds that value constant to use later in the % complete measure. In the charts and tables I've been experimenting with so far, I can get the number of tasks completed in each month, but not as a running total for a nonstandard calendar year (Apr-Mar), which is the value I will need to calculate and display the % change by month as the year progresses. I can get this measure to work as a yearly total just by dividing measure 2 (completed task count) by measure 1 (total task count), but as soon as I try to incorporate the monthly timeline it all falls apart. Any assistance or direction would be greatly appreciated! Thanks
