Running Total

[Chockers1]

Hi ALl,

I have created the below DAX which calucaltes the running averages for "Bat Inns" which is everytime a batter has batted in my database, this is set as a column.

Bat_RunningTotal_Bat_Innings_Col =

SUMX(

    FILTER(

        ALL(Bat),

        Bat[Player ID] = EARLIER(Bat[Player ID]) && Bat[Start Date] <= EARLIER(Bat[Start Date])

    ),

    IF(ISBLANK(Bat[Bat Inns]), 0, 1)

)

It works great!

The issue is when it's in a table on a dashboard it doesn't up it as a sequnce - eg, 1, 2, 3, 4 etc... It gets show by "Start Date" in which they may have batted twice.

in a matrix table it doesnt show each individual "Inns" if the batter batted twice in a match, anyone know how to fix it?

?

[Anonymous]

Hi @Chockers1 ,

Assuming you have a column [match id] in your Bat table that uniquely identifies each match, please try:

Bat_RunningTotal_Bat_Innings_Col =
SUMX (
    FILTER (
        ALL ( Bat ),
        Bat[Player ID] = EARLIER ( Bat[Player ID] )
            && Bat[Start Date] <= EARLIER ( Bat[Start Date] )
            && (
                Bat[Start Date] < EARLIER ( Bat[Start Date] )
                    || Bat[match id] <= EARLIER ( Bat[match id] )
            )
    ),
    IF ( ISBLANK ( Bat[Bat Inns] ), 0, 1 )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@Chockers1 , Not very clear, But try a measure like

Bat_RunningTotal_Bat_Innings_Col =
SUMX(
FILTER(
ALL(Bat),
Bat[Player ID] = MAx(Bat[Player ID]) && Bat[Start Date] <= MAx(Bat[Start Date])
),
IF(ISBLANK(Bat[Bat Inns]), 0, 1)
)