Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Consta
Frequent Visitor

Burndown Graph for projects based on date

Hello all.
I have already started a topic here, but now I would like to be more specific.
So far I have created and used a burndown chart in Excel, now I want to display the same in Power BI.
The following graph should be created:

8b014365ef
Previously created in Power BI:

Capture.PNG

I don't quite know how to display the remaining projects. The logic behind reamining projects is that it takes the total number of projects at the beginning (20 - Q3) and each quarter subtracts the starting projects from the previous quarter (e.g. in total there are 160 projects, 20 - Q3 start 5, so remaining projects 20 - Q4 are 155).
Also I want to create an equal distribution. This means also start at the maximum number, but subtract each quarter a fixed value (number of projects / number of quarters).

Starting projects per quarter works fine!


I have a big problem with displaying such simple formulas in Power BI.
I am super grateful for all answers! Maybe I am just too stupid.
Testfile: https://easyupload.io/kmk4my

Best regards

 

UPDATE:

Got the remaining projects with this formula:

Count projects = calculate(DISTINCTCOUNT('211004 Export'[Project Name])+1, filter(allselected('211004 Export'), '211004 Export'[Planned start date] > min('211004 Export'[Planned start date])))
c15bf24d37

But it starts way too early and goes one quarter further.

Any suggestions how to solve this?

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Consta 

 

I started by creating a calendar table to get consecutive columns of dates. Then create three measures to be used as values. I put my pbix file in the attachment you can reference.

Result :

vchenwuzmsft_0-1634116341812.png

 

In fact, if the date starts too long, you might consider using this setting below to specify the time period you need.

vchenwuzmsft_1-1634116341814.png

 

If something wrong, please let me know.

 

Best Regards

Community Support Team _ chenwu zhu

 

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

View solution in original post

8 REPLIES 8
v-chenwuz-msft
Community Support
Community Support

Hi @Consta 

 

I started by creating a calendar table to get consecutive columns of dates. Then create three measures to be used as values. I put my pbix file in the attachment you can reference.

Result :

vchenwuzmsft_0-1634116341812.png

 

In fact, if the date starts too long, you might consider using this setting below to specify the time period you need.

vchenwuzmsft_1-1634116341814.png

 

If something wrong, please let me know.

 

Best Regards

Community Support Team _ chenwu zhu

 

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

Wow.

That was what I need! Thank you.

I want to understand the equal distr. and start_project measures.

 

Can you explain what you did there? I want to learn. 

Your solution looks great.

 

And: Why doesnt this work instead of the measure start_projects: Drag the Project Name as a count to the values of the chart. 

 

Hi @Consta,I am glad you are satisfied with this result.

The visual conversion from line chart to table allows you to see the underlying data more visually.

equal distribution =
VAR _total =
    DISTINCTCOUNT( 'Table'[Project Name] ) // How many project names are there in total
VAR _diff =
    DATEDIFF(
        MINX( ALL( 'Table 2' ), [Date] ),
        MAXX( ALL( 'Table 2' ), [Date] ),
        QUARTER
    ) // How many quarters are there in total
VAR _1 =
    DATEDIFF(
        MINX( ALL( 'Table 2' ), [Date] ),
        DATE( SELECTEDVALUE( 'Table 2'[Date].[Year] ), 3 * ( SELECTEDVALUE( 'Table 2'[Date].[QuarterNo] ) ), 1 ),
        QUARTER
    ) //Total number of quarters starting from the smallest date to the currently selected date (function of selectedvalue)
VAR _result = _total - _1 * ( _total / _diff ) // for example: 160 ProName - 2 months have passed* (average ProName starting each month)
RETURN
    IF( _result > _total, BLANK(), _result )
// when blank, it will hidden the year and quarter.
start_projects =
CALCULATE(
    DISTINCTCOUNT( 'Table'[Project Name] ),
    FILTER(
        'Table',
        [Planned start date].[Quarter] = SELECTEDVALUE( 'Table 2'[Date].[Quarter] )
            && [Planned start date].[Year] = SELECTEDVALUE( 'Table 2'[Date].[Year] )
    )
)//Count the number of non-duplicates in a filtered table. The filtering condition is that the quarter and year in the planned start date are equal to the values in the first and second columns of the current table.

For the first row, the selectedvalue() is the 2020-3 quarter.

vchenwuzmsft_0-1634119473019.png

Did I explain myself clearly? You can ask me again if you have any questions.

Best Regards

Community Support Team _ chenwu zhu

 

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

 

I understand thank you!

When I use the measures for the planned end dates (I adapted the measure to refer to this column) I get a gap from the remaining projects. X Axis is on categorized. Do you know a fix for this?

When i choose continous this problem is fixed but i only can see january and july in the drilldown not the quarters. So its 2020 July, 2023 January etc.. instead of 2020-Q3,2020-Q4..

Unbenannt.PNG

Hi,@Consta

Yes,when catagorized, it will be 2020-Q3,2020-Q4. But with continous, it will be a continuous X Axis. I thought about using this approach at first, but I saw that your requirement was in the form of something like 2020-Q3, so I chose to write measure to achieve it. There is no way to fix it directly at the moment, you can suggest it in power Bi ideas.
I see that your chart still has the early start quarter, trying to use a combination of if() and blank() will keep it from showing.

How exactly should i use if() and blank()? 

Thanks for the help.

end =
VAR _c =
    CALCULATE(
        DISTINCTCOUNT( 'Table'[Project Name] ),
        FILTER(
            'Table',
            [Planned end date].[Quarter] = SELECTEDVALUE( 'Table 2'[Date].[Quarter] )
                && [Planned end date].[Year] = SELECTEDVALUE( 'Table 2'[Date].[Year] )
        )
    )
RETURN
    IF(
        SELECTEDVALUE( 'Table 2'[Date].[Year] ) <= 2020
            && SELECTEDVALUE( 'Table 2'[Date].[QuarterNo] ) < 3,
        BLANK(),
        _c
    )

Nothing changes with this solution, sadly 

The X axis still starts at 2020 Q2, not 2021 Q2

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors