Filtering Cumulative count by date

[Anonymous]

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

[v-lili6-msft]

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]

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 ?

[v-lili6-msft]

hi @Anonymous 

Please re-upload the attachment, that link seems to be wrong.

For all came out with the same value, you should add a conditional in the formula like this:

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

If you still have the problem, Please share a simple sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft  Lin,

Apologies for the delay in responding, I've finally managed to find a solution to the inability to attach files here and have saved the speadsheet on OneDrive,  <here>

It contains sample data and field descriptions, as per the screenshots in the posts already sent

Thanks in advance for any help/solution you can provide

Regards

Fred

[Anonymous]

@v-lili6-msft 

Hmm, the file is in my personal dropbox, it wants an email address to share with and I have no address to enter.  I can also find no way to share files, apart from photos.  Any system I use requires an email to share with, how can I proceed please ?

I've tried typing in the data, a slow process and it won't allow copy/paste, so to provide data it looks like I need to either take a picture and share that or type in the first row and then attach a picture of the remainder.  Help !

Thanks again for your time Lin, and if someone can advise how to upload data/files or a way to share I'd be most grateful

Fred

[Anonymous]

@v-lili6-msft 

below are snapshots of the data (with the Power BI field definitions) and then the measures I'm using. plus a resulting visual and shot of the data in the "Cumulative" measure entries, all showing 21.

In the earlier shot, showing the chart that was produced, it gives 21 as the result for all the measures, even if there is no data that obeys the filters, I tried adding the additional filter you suggested but it made no difference

[Anonymous]

@v-lili6-msft   Here's a shot of what I'd hope to achieve, admittedly the date scale is wrong as I'm looking for a monthly summary, but this was a quick'n'dirty in Excel

[Anonymous]

stored in Dropbox, here 

[Anonymous]

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])

    ))