Desktop

[aznariy]

Hello, 

Seems like I implemented the formula from here https://community.powerbi.com/t5/Desktop/Cumulative-Count-by-Date/td-p/368918 but that didn't work. 

I'm trying to display my severities by month cumulative: in other words add value of the previous cell in each next cell of every row. Please refer to the picture below, where values are not adding to each other, rather staying there as standalones, and only sum up at the most last column called Total. 

So far I've tried these formulas:

CumulativeSeverity = 
CALCULATE (
SUM ( Table[Month-Year] ),
FILTER (
ALLSELECTED( Table[Month-Year] ),
YEAR( Table[Month-Year] ) = YEAR ( MAX ( Table[Month-Year] ) )
&& Table[Month-Year] <= MAX ( Table[Month-Year] )
)
)

Getting this interesting message

Not sure what String PowerBi is complaining about, in the entire formula I only use Date formats

Another try was

CumulativeSeverity = CALCULATE(
COUNTA(Table[Month-Year]),
FILTER(ALL(Table), Table[Month-Year] <= MAX(Table[Month-Year])), VALUES(Table[Severity]))

Is this ever possible in PowerBi Desktop to calculate rows cumulatevely? 

[v-qiuyu-msft · Accepted solution]

Hi @aznariy,

The column Table[Month-Year] data type isn't number, so the error occurs when you use SUM ( Table[Month-Year] ). 

For your requirement, you can modify the measure like below: 

CumulativeSeverity = 
IF (
    ISINSCOPE ( 'Table'[Month-Year] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Month-Year] ) = YEAR ( MAX ( 'Table'[Month-Year] ) )
                && 'Table'[Month-Year] <= MAX ( 'Table'[Month-Year] )
                && 'Table'[Severity] = MAX ( 'Table'[Severity] )
        )
    ),
    SUM ( 'Table'[Amount] )
)

You can download the attached pbix file to test it. 

Best Regards,
Qiuyun Yu 

Sample1210.pbix

[v-qiuyu-msft · Accepted solution]

Hi @aznariy,

The column Table[Month-Year] data type isn't number, so the error occurs when you use SUM ( Table[Month-Year] ). 

For your requirement, you can modify the measure like below: 

CumulativeSeverity = 
IF (
    ISINSCOPE ( 'Table'[Month-Year] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Month-Year] ) = YEAR ( MAX ( 'Table'[Month-Year] ) )
                && 'Table'[Month-Year] <= MAX ( 'Table'[Month-Year] )
                && 'Table'[Severity] = MAX ( 'Table'[Severity] )
        )
    ),
    SUM ( 'Table'[Amount] )
)

You can download the attached pbix file to test it. 

Best Regards,
Qiuyun Yu 

Sample1210.pbix