Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering Cumulative count by date

I'm working my way through the creation of a new report, with significant help from members of this group.

I need to produce a Line and Column visual, showing, cumulatively, the number of project milestones scheduled per month (the line) and the number that have been delivered, delivered early, late,  that are overdue or are future milestones (stacked columns by month).  This is all taken from the MS Project Tasks table.  I've got the Line but when I put DAX in place to pick the column figures, the filter doesn't seem to kick in.  If anyone can advise what's the correct way to code the Cumulative code I'd be most grateful.

 

The TaskMilestoneStatus is a Whole Number column,

 

TaskMilestoneStatus =
    if(and(
        Tasks[TaskPercentCompleted]=100,
        (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskLateFinish].[Date],DAY)=0)),1,
    if(and(
        Tasks[TaskPercentCompleted]=100,
        (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskLateFinish].[Date],DAY)>0)),2,
    if(and(
        Tasks[TaskPercentCompleted]=100,
        (Datediff(Tasks[TaskStartDate].[Date],Tasks[TaskLateFinish].[Date],DAY)<0)),3,
    if(Tasks[TaskStartDate]>TODAY(),5,
    if(and(
        Tasks[TaskPercentCompleted]<>100,
        (Datediff(Tasks[TaskStartDate].[Date],TODAY(),day)>0)),4
        )))))

 

Below is an example of the code for Delivered Milestones.

 

Cumulative Delivered =
CALCULATE (
    COUNT( 'Tasks'[MilestoneDate] ),
    FILTER (
        ALLEXCEPT( 'Tasks',Tasks[ProjectName]),
            NOT( NOT( 'Tasks'[TaskIsMilestone])
            && (Tasks[TaskMilestoneStatus])=1)
            && 'Tasks'[TaskFinishDate] <= MAX ('Tasks'[TaskFinishDate])
    ))
 
Regards
Fred
Need Help

9 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    HI, Anonymous 

    Try to use ALLSELECTED instead of ALLEXCEPT in the formula like:

    Cumulative Delivered =
CALCULATE (
    COUNT ( 'Tasks'[MilestoneDate] ),
    FILTER (
        ALLSELECTED ( 'Tasks' ),
        Tasks[ProjectName] = MAX ( Tasks[ProjectName] )
            && NOT ( NOT ( 'Tasks'[TaskIsMilestone] )
            && ( Tasks[TaskMilestoneStatus] ) = 1 )
            && 'Tasks'[TaskFinishDate] <= MAX ( 'Tasks'[TaskFinishDate] )
    )
)

    If not your case, please share a simple sample pbix file for us have a test.

     

    Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lili6-msft Lin,

      Thanks for the suggestion, it works . . . . sort of. 

       

      I replicated the code, to point at the different TaskMilestoneStatus values, but all came out with the same value, namely the number of Milestones.  Attached is a CSV with the data and field definitions plus copies of the Measures I've used

       

      Hmm, can't see a way to attach the CSV, am I missing something ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        stored in Dropbox, here 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    What I omitted to say was that the Cumulative Delivered DAX results in a count of all tasks in the project plan, rather than a count of the Milestones (TaskIsMilestone - a True/False MSProject field).  The Line code (below) works, giving a correct result of 43, whereas the Delivered count returns 274.

    Cumulative Milestones =
    CALCULATE (
        COUNT( 'Tasks'[MilestoneDate] ),
        FILTER (
            ALLEXCEPT( 'Tasks',Tasks[ProjectName]),
    NOT( NOT( 'Tasks'[TaskIsMilestone]))
                && 'Tasks'[TaskFinishDate] <= MAX ('Tasks'[TaskFinishDate])
        ))