Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Creating a measure that computes cumulative sum and is also responsive to the evaluation context

Let's think of a project management scenario where projects status change from Open (1) to In-Progress (2) and finally Closed (3). In this example I have 10 projects. My requirement is to produce a count of projects based on a date and/or status type. The key thing here is that the value returned by the measure is related to the evalutation context.


Here's an example of the Project Status table:




Now think of this visualization below:



For example: on 6th Feb we had 4 projects in the system where 2 were open, 1 was In-Progress and 1 was closed. That gives me a total of 4! It looks at the data of 7 rows which is self and the previous 6 rows. If want to know the status of the projects as of today we just need to look at the last entry on 24th Dec which will give me 10.


When I display this measure which calculates the number of projects on a table visual without dates and status types, I would expect 10. In this scenario my context filter doesn't have dates and status types to filter by.


If my visualization (matrix) only has Status Type as the column field I would expect:



If my matrix has Date as a row field and Status Type as column field then I would expect to see what I've shown earlier.


What I'm earnestly asking is for your advice on the best way to tackle this challenge. As a first attempt I've come up with something which is not giving me no where near what I expect but I would appreciate your comments if I'm tackling this the right way.




Project Status Count = 

    VAR MaxKnownDate =
        MAX ( 'Project Status'[Status Date] )

    VAR StatusSummaryBase =
        SUMMARIZE (
     VAR StatusSummary =
            ADDCOLUMNS (
                  "Latest Count", 
                  VAR DateSnapshot = 'Date'[Date]
                    CALCULATE (
                        COUNTX (
                             'Project Status',
                              'Project Status'[Project Id]
                        LASTNONBLANK (
                                'Date'[Date] <= DateSnapshot
                            COUNTROWS(RELATEDTABLE('Project Status'))
            ,FILTER (
                ALL ( 'Project Status'[Status Date] ),
                'Project Status'[Status Date] <= MaxKnownDate

    VAR Result =
        SUMX(StatusSummary, [Latest Count])




I understand I may still need to check for the presence of context filters such as data and status type in order to produce the right calculation.



Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors