Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
scooby91
New Member

Cumulative count with null values

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

 

Data.PNG

Snip.PNG

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!

 

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

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;

 

11.PNG

 

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.

 

22.PNG

 

Thanks,
Xi Jin.

View solution in original post

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

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;

 

11.PNG

 

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.

 

22.PNG

 

Thanks,
Xi Jin.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.