How to create a measure calculating Cumulative Count by Year based on a specific value

[ineedham]

I am trying to create a measure that returns the cumulative total of Pro licenses each year. The data looks like the table on the left, and I want it to return the values on the right:

I’m able to create the following measure:

Cumulative Licenses =
CALCULATE (
    COUNTX ( 'Power BI Licenses', [License Status] = "Pro" ),
    FILTER (
        'Power BI Licenses',
        [Pro License Start Year]
            <= MAX ( [Pro License Start Year] )
    )
)

…but it won’t work in the line chart visual.  I get this error:

MdxScript(Model) (4, 86) Calculation error in measure Power BI Licenses'[Cumulative Licenses]: The function COUNTX cannot work with values of type Boolean.

I tried it with COUNTAX instead of COUNTX, and it works with the visual, but doesn’t return the running totals.

Also not having luck with 

COUNTROWS(

              FILTER(

                   SUMMARIZE (

[v-piga-msft · Accepted solution]

Hi @ineedham ,

For your requirement, please try the measure below.

Measure = 
IF (
    MAX ( 'Table1'[License Status] ) = "",
    BLANK (),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[Pro License start year] <= MAX ( 'Table1'[Pro License start year] )
                && 'Table1'[License Status] <> ""
        )
    )
)

Here is the output.

You also could have a reference of my attachment.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best  Regards,

Cherry

[ineedham]

That worked.  Thank you!