topic Re: Trying to track % complete over time and contrast with past periods in line graph in DAX Commands and Tips

Trying to track % complete over time and contrast with past periods in line graph

JBI1250 — Tue, 27 Sep 2022 21:49:18 GMT

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

Re: Trying to track % complete over time and contrast with past periods in line graph

v-xiaosun-msft — Wed, 28 Sep 2022 10:23:01 GMT

Hi JBI1250 ,

According to your description, here is my solution.

Create a column to meet your request1:

Column = CALCULATE ( COUNT ( 'Tabelle1'[Task ID] ), FILTER ( 'Tabelle1', 'Tabelle1'[Task Year] = EARLIER ( Tabelle1[Task Year] ) ) )

Create another column to meet your request2:

Column 2 = CALCULATE ( COUNT ( 'Tabelle1'[Task ID] ), FILTER ( 'Tabelle1', 'Tabelle1'[Task Year] = EARLIER ( Tabelle1[Task Year] ) && 'Tabelle1'[Task Status] = "Complete" ) )

Create a measure to meet your request3:

Measure = DIVIDE ( MAX ( [Column 2] ), MAX ( [Column] ) )

Final output:

Can you describe the basis of the line chart in more details to better solve your problem?

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Trying to track % complete over time and contrast with past periods in line graph

JBI1250 — Wed, 28 Sep 2022 14:49:42 GMT

Thanks so much for your response!

The line chart is meant to track the progress over the year as tasks are completed, so at the beginning of the year (april) we would start of with a set number of tasks for the year, say 300. Every time we complete a task, we record the date the task was completed. As the year progresses and more tasks are completed, the % of completed tasks relative to the total number of tasks increases and that is what I'm trying to display with the line chart. So for example, by June, maybe we've completed 10% of the tasks for 2022 and maybe 20% by July. I'd like to be able to contrast against previous year's progress to show how the current pace compares with previous years.

I hope that provides some further clarity, but if that's not what you were looking for, please let me know and please let me know specifically what other info or explanation i can offer 🙂

Thanks again and I will attempt to implement your solution asap and let you know if it solved my issues!

Re: Trying to track % complete over time and contrast with past periods in line graph

JBI1250 — Wed, 28 Sep 2022 15:47:33 GMT

Hi Xiaosun, i've implemented your suggestions, but it doesn't quite seem to be working as expected.

When i link my Date table to the Start Date field in my fact table, i get the annual totals, but don't seem able to get a running monthly total.

When i switch the relationship from start date to date completed, i get a monthly breakdown, but it appears that it's all the 2019 annual total and not a running/compounding/aggregating total based on year and month...

Interestingly, the line graph seems to be showing the correct annual or current % complete totals based on task year (ie. 2022 is down at around 24%, while 2019 is up at 90%), it's just not showing the rate of completion progressing over the months.

The matrix on the left isn't displaying the correct totals for each year (only 2019 it appears), but i think this is because I'm using my date table as the source for the Rows, whereas i'm using Task Year for the Legend in the line graph...

Thanks again for your help! 🙂