Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Cumulative Line and Stacked Column visual

  • 1.  Cumulative Line and Stacked Column visual

    Posted 26 days ago

    Hi All, I'm trying to develop a cumulative visual that displays the number of project milestones over the lifetime of a project.  I want to show the milestones delivered on time, early or late and those that are in the future.

     

    I've created a measure, defined as a Whole Number, to set an indicator value to reflect the above.

     

    TaskMilestoneStatus =

        if(and(

            Tasks[TaskPercentCompleted]=100,

            (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskActualFinishDate].[Date],DAY)=0)),1,

        if(and(

            Tasks[TaskPercentCompleted]=100,

            (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskActualFinishDate].[Date],DAY)>0)),2,

        if(and(

            Tasks[TaskPercentCompleted]=100,

            (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskActualFinishDate].[Date],DAY)<0)),3,

        if(Tasks[TaskStartDate]>TODAY(),5,

        if(and(

            Tasks[TaskPercentCompleted]<>100,

            (Datediff(Tasks[TaskStartDate].[Date],TODAY(),day)>0)),4

            )))))

     

    Plus a set of appropriately named cumulative measures (defined as Whole Numbers) that feed into the visual as stacked columns, with a line representing the overall cumulative total milestones

     

    Cumulative Delivered =

    CALCULATE (

        COUNT('Tasks'[TaskStartDate]),

        FILTER (

            ALLSELECTED('Tasks'),

            Tasks[ProjectName]=MAX(Tasks[ProjectName])

                && NOT(NOT('Tasks'[TaskIsMilestone])

                && ('Tasks'[TaskMilestoneStatus])=1)                (or 2,3,4 or 5)

                && ('Tasks'[TaskFinishDate] <= MAX ('Tasks'[TaskFinishDate])

                && ('Tasks'[ProjectName] = MAX ('Tasks'[ProjectName])

        ))))

     

    Where 1=Delivered on time, 2=Late, 3=Early, 4=Overdue, 5=Future

     

    Cumulative Milestones =

    CALCULATE (

        COUNT( 'Tasks'[MilestoneDate]),

        FILTER (

            ALLEXCEPT('Tasks',Tasks[ProjectName]),

                NOT(NOT('Tasks'[TaskIsMilestone]))

                && ('Tasks'[TaskFinishDate] <= MAX ('Tasks'[TaskFinishDate])

        )))

     

    The resulting visual shows all entries having the same value, so instead of the columns stacking and reaching the line, they all have the same value, namely the cumulative total milestones for that period.

    The attached PNG shows the resulting visual, the spreadsheet is the data used and a chart created in Excel from that data.

    Any suggestions/corrections gratefully received
    Regards
    Fred

    Attachment(s)

    xlsx
    Milestone data.xlsx   28K 1 version
    Conference-PBI_200x200