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 ...
Need Help
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 

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

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

        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