Return values for blanks

[Anonymous]

Hello everyone, 

I am comparing statistics from Messi and Ronaldo. I have column with Date, Player and Goals. And I created a cumulative measure, which is as follows:

Cumulative goals = 

CALCULATE (

SUM ( 'Stats'[Goals] ),

FILTER (

ALL ( 'Stats' ),

'Stats'[Date] <= MAX ( 'Stats'[Date] )

&& 'Stats'[Player] = MAX ( 'Stats'[Player] )

))

The result is show in the left table:

I am looking for output as in the right column. How can I change my measure to get to output with '0' for the one when the other one scored?

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

1.Create a new table.

Table =
GENERATE (
    VALUES ( Stats[Date] ),
    UNION ( ROW ( "Player", "Messi" ), ROW ( "Player", "Ronaldo" ) )
)

2.Create a calculated column in the new table.

Goal =
IF (
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    )
        <> BLANK (),
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    ),
    0
)

3.Create a measure.

Cumulative goals =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && 'Table'[Player] = MAX ( 'Table'[Player] )
    ),
    'Table'[Goal]
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yanjiang-msft This is great!! Thank you so much!

[Anonymous]

@amitchandak I don't, but I can create a date table. It will be a one-to-one relationship between the date column in my Stats table to the Date[date]. So, I am not sure if that work, but the Date table isn't linked to a specific player, if you're aiming for that.

[amitchandak]

@Anonymous , Try with +0

Cumulative goals =
CALCULATE (
SUM ( 'Stats'[Goals] ),
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] <= MAX ( 'Stats'[Date] )
&& 'Stats'[Player] = MAX ( 'Stats'[Player] )
))+0

[Anonymous]

@amitchandak Thank you, and that would work, but in this case, it doesn't. 

I think it's because there's only one day per player: 

When Ronaldo scores and Messi doesn't, you have a row with the specific date, the number of goals and Ronaldo. 

[amitchandak]

@Anonymous , do you have a Separate date and player table ? In that case this might work