How to create a DAX measure that computes cumulative sum and is also responsive to the evaluation context in Power BI

Question

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 the Project Status table:

Now have a look at the table visual below which is the expected result, at least in part.

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 projects as of 6th Feb. It looks at the data of 7 rows which is self and the previous 6 rows. If I 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 columns, I would expect the value of 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 rather than trying to fix what I've got I would appreciate your comments if I'm tackling this thing the right way.

Project Status Count = 

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

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

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

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.

Answer 1

Follow these below steps to achieve your required output-

Step-1: Create a Calculated Column with this below code-

date_part = 
DATE(
    YEAR(your_table_name[status date]),
    MONTH(your_table_name[status date]),
    DAY(your_table_name[status date])
)

Step-2: Now create this below 4 Measures-

Open (1) = 

var current_row_pid = MIN('your_table_name'[project id])
var current_row_date_part = MIN('your_table_name'[date_part])

var _table1 = 
SUMMARIZE(
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[date_part] <= current_row_date_part
    ),
    'your_table_name'[project id],
    "project status id", MAX('your_table_name'[project status id])
)

var _table2 = 
ADDCOLUMNS(
    _table1,
    "status type id", 
    LOOKUPVALUE(
        'your_table_name'[status type id],
        'your_table_name'[project id],[project id],
        'your_table_name'[project status id],[project status id]
    )
)

RETURN 
COUNTROWS(
    FILTER(
        _table2,
        [status type id] = 1
    )
) + 0

in-progress (2) = 

var current_row_pid = MIN('your_table_name'[project id])
var current_row_date_part = MIN('your_table_name'[date_part])

var _table1 = 
SUMMARIZE(
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[date_part] <= current_row_date_part
    ),
    'your_table_name'[project id],
    "project status id", MAX('your_table_name'[project status id])
)

var _table2 = 
ADDCOLUMNS(
    _table1,
    "status type id", 
    LOOKUPVALUE(
        'your_table_name'[status type id],
        'your_table_name'[project id],[project id],
        'your_table_name'[project status id],[project status id]
    )
)

RETURN 
COUNTROWS(
    FILTER(
        _table2,
        [status type id] = 2
    )
) + 0

closed (3) = 

var current_row_pid = MIN('your_table_name'[project id])
var current_row_date_part = MIN('your_table_name'[date_part])

var _table1 = 
SUMMARIZE(
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[date_part] <= current_row_date_part
    ),
    'your_table_name'[project id],
    "project status id", MAX('your_table_name'[project status id])
)

var _table2 = 
ADDCOLUMNS(
    _table1,
    "status type id", 
    LOOKUPVALUE(
        'your_table_name'[status type id],
        'your_table_name'[project id],[project id],
        'your_table_name'[project status id],[project status id]
    )
)

RETURN 
COUNTROWS(
    FILTER(
        _table2,
        [status type id] = 3
    )
) + 0

total_project = [Open (1)] + [in-progress (2)] + [closed (3)]

Here is the final output-