Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I'm trying to create a graph that shows the expected burndown of milestones of a program. I would like to compare the planned dates with the actual dates. My goal would be two lines that start 25 and decline to 0 over time.
I have a table with numerous names for milestones, where a milestone name can appear multiple times for different projects [Milestone Name] , and a date on which the milestons will be completed [End date approved]. A sample of the data that i would like to use looks like this:
Planned date | Actual date | Milestone Name |
31/12/2020 | 29/12/2020 | Example 1 |
31/12/2020 | 31/12/2020 | Example 2 |
30/04/2021 | 17/03/2020 | Example 3 |
30/06/2021 | 05/07/2020 | Example 4 |
30/09/2021 | 30/09/2021 | Example 5 |
30/10/2021 | 25/9/2021 | Example 6 |
30/10/2021 | 25/9/2021 | Example 7 |
30/10/2021 | 25/9/2021 | Example 8 |
30/10/2021 | 30/9/2021 | Example 9 |
30/10/2021 | 30/9/2021 | Example 10 |
30/10/2021 | 30/9/2021 | Example 11 |
30/10/2021 | 30/9/2021 | Example 12 |
30/10/2021 | 15/10/2021 | Example 13 |
30/10/2021 | 15/10/2021 | Example 14 |
30/10/2021 | 15/10/2021 | Example 15 |
30/10/2021 | 15/10/2021 | Example 16 |
30/10/2021 | 15/10/2021 | Example 17 |
30/10/2021 | 15/10/2021 | Example 18 |
30/10/2021 | 10/11/2021 | Example 19 |
30/10/2021 | 10/11/2021 | Example 20 |
30/10/2021 | 10/11/2021 | Example 21 |
30/10/2021 | 25/10/2021 | Example 22 |
31/12/2021 | 31/12/2021 | Example 23 |
31/12/2021 | 31/12/2021 | Example 24 |
31/03/2022 | 28/01/2022 | Example 25 |
The graph would ideally starts at the first date, with the total amount of milestones to go, and should end at 0 on the last date (31-03-2022). Something like this:
(applogies for the paint drawing)
Solved! Go to Solution.
Hi @Anonymous
Try below measures.
Planned to do =
VAR _count =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) )
) + 0
RETURN
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Planned date] ) ), BLANK (), _count )
Actual to do =
VAR _count =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
) + 0
VAR _date =
CALCULATE (
SELECTEDVALUE ( 'Table'[Actual date] ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
)
RETURN
IF ( ISBLANK ( _date ), BLANK (), _count )
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
@Anonymous , with help from date table like
Cumm Sales = CALCULATE(count(Table[Milestone]),filter(allselected(Date),Date[Date] >=max(Date[Date])))
or
Cumm Sales = CALCULATE(count(Table[Milestone]),filter(allselected(Table),Table[Planned Date] >=max(Table[Planned Date])))
Hi Amitchandak,
Thank you for the quick reply. I tried your solution, but it doesn't end at 0. It appears that the entire graph should shift one date entry, if that makese sense. Do you know if the measure can be adjusted so that it does?
Kind regards,
Odi
Hi @Anonymous
It seems ending at 1 is correct because on the last date (31-03-2022) there is the last one Milestone Example 25. If you want it to show the total amount of milestones to go (not including current day), you could replace >= with > in Amit's formula. But in this way, it will not start at 25 as the milestones on the first day will be filtered out.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @v-jingzhang ,
Thank you for your answer!
I understand that the measure ends at 1, since the last date has one value. I think the measure works the way it should, unforunately though, it doesn't achieve what i'm trying to achieve. I tried changing >= to >, but it doesn't reach my goal.
I would like to get a graph that ends at 0, based on the idea that if all milestones have indeed been finished by the last date the "to do" value should be 0 and not 1.
This might not be something that is possible to achieve with a measure, I wouldn't know.
The reason I would like to get to 0 is that in my real data I have 150+ milestones at the last date, which results in a burndown graph that ends at 150+ somewhere in the middle of the graph, which is not the expected end, because you expect to end at 0. Hence the business is not happy with my graph 😉
I hope this is a clear explanation and I'm curious to know if there is a solution for it. A measure would be really nice, but if there is another way that would also be good.
Kind regards,
Odi
Hi @Anonymous
Does my reply solve your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.
Regards,
Community Support Team _ Jing
Hi @v-jingzhang ,
Apollogies for not replying before. I've looked at your solution and with the +0 the graph does indeed go to 0, which is very nice!
I did notice that with >= it doesn't reach 0, but with > it does. As you already mentioned it then filters out the first date, which is a pity, but not limiting.
I'm still struggling with one thing, and that is to get both lines (measures) correctly in one graph. I understood from @amitchandak that I have to use a date table?
I've created a table with all dates from 2020 to 2023, but if I try to use the date from that table on my x-axis it doesn't work. I can't relate both date columns to the one calendar... Could very well be that i'm not doing the right thing here, so if you could assist on that it would be great.
I've posted a picture below, i hope it's visible. I would add the pbix file but I can't upload it to the message, and I'm not allowed to share via dropbox etc.
Kind regards,
Odi
Hi @Anonymous
Only one active relationship is allowed to exist between two tables so you met this problem. We have a common solution to deal with it by using USERELATIONSHIP() function to activate an inactive relationship in this measure.
Suppose you have an active relationship on Planned Date column, you can use previous measure to get the result. At the same time, you can create an inactive relationship between Actual Date column and 'Date'[Date] column. Then you can use below measure to calculate the value based on actual date.
Actual to do =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
) + 0
Reference: Active And Inactive Data Relationships In Power BI
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @v-jingzhang,
Thanks a lot! I get the following visual now:
It does everything I've asked of you. If I may be so bold as to ask one more question:
Is it possible to tweek the visual or data such that the graph doesn't show every single date, but only the dates that with data points in them? Making the lines look more fluid:
instead of
If there is a solution for that I would be very gratefull. We're using these graphs throughout our organisation.
Kind regards,
Luca
Hi @Anonymous
Try below measures.
Planned to do =
VAR _count =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) )
) + 0
RETURN
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Planned date] ) ), BLANK (), _count )
Actual to do =
VAR _count =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
) + 0
VAR _date =
CALCULATE (
SELECTEDVALUE ( 'Table'[Actual date] ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
)
RETURN
IF ( ISBLANK ( _date ), BLANK (), _count )
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @Anonymous
It is because blank values don't show in the chart. You could add +0 at the end of the measure.
Planned to do =
CALCULATE (
COUNT ( 'Table'[Milestone Name] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) )
) + 0
Regards,
Jing
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |