Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Burndown Graph with deadlines

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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-jingzhang ,

    Beautiful!

    Thank you so much for your help!

    Kind regards,
    Odi