Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-
I think I need 3 measures/variables:
The problems I've been having-
Any assistance or direction would be greatly appreciated!
Thanks
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! 🙂
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |