Cumulative count with null values

[scooby91]

Hello,

I'm trying to do a cumulative count based on dates, however, I am encountering problems with null values.

The code based on start dates is below, which produces the desired result:

Cumulative Start = 
CALCULATE( 
    COUNT( Test[Start Date] ), 
    FILTER ( 
    ALL ( Test ), 
    Test[Start Date] <= MAX( Test[Start Date] ) 
        ) 
) + 0

However, if I mirror this code for end dates (which contain null values), I do not get the same pattern:

Cumulative End = 
CALCULATE(
    COUNT( Test[End Date] ),
    FILTER (
    ALL ( Test ),
    Test[End Date] <= MAX ( Test[End Date] )
    )
) + 0

Furthermore, if I select a month on the slicer with no corresponding date value, the table / graph will naturally display 0. I would like this to display the cumulative total of the previous month e.g. June 2017 cumulative start total would be 8 (May 2017 figure).

My questions are twofold:

1. What is the code that will generate the End Count in the same manner as the Start Count?

2. How would I return the previous month's cumulative total?

Thanks in advance!

[v-xjiin-msft · Accepted solution]

Hi @scooby91,

1. In your scenario, the Cumulative End should work. The issue is not related to the expression. It is because that the MonthYearNo is based on Start Date. If you create a new MonthYearNo based on End Date and put it with Cumulative End into another table. The result should be right. Please refer;

2. For this scenario, you can try to calculate the Cumulative count based on slicer (Table 2) MonthYearNo not based on your source table (cumulative) MonthYearNo. 

To achieve this, first we should convert MonthYearNo to number type:

MonthYearNo = YEAR('Calendar'[Date])*100 + MONTH('Calendar'[Date] )

Then try following measure:

New Cumulative Start =
CALCULATE (
    COUNTROWS (
        FILTER (
            ALL ( cumulative ),
            cumulative[MonthYearNo] <= MAX ( 'Table 2'[MonthYearNo] )
        )
    )
)

Then it will display the cumulative count for each month even the month doesn't have corresponding value in source table.

Thanks,
Xi Jin.

[scooby91]

Hi @v-xjiin-msft, thanks for the solution! Much appreciated!