How to calculate cumulative total and running rate

[Anonymous]

Hello Team,

I have excel in below format, 

I am able to calculate Running Rate and cumulative count in Excel, but some how I am not replicate same in PowerBI,  Please let me know what I am missing here.

Thanks in Advance

Avian

[Anonymous · Accepted solution]

Thanks for Kedar_Pande's concern about this issue.

Hi, @Anonymous 

I am glad to help you.

Perhaps you can refer to my DAX, I hope that helps:

First create Measure Cumulative Count :

Cumulative Count = 
CALCULATE(
    SUM('Table'[Count]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Year-Mon] <= MAX('Table'[Year-Mon])
    )
)

Then create a Measure Running Rate:

Running Rate = 
DIVIDE(
    SUMX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year-Mon] <= MAX('Table'[Year-Mon])
        ),
        'Table'[Status1] + 'Table'[Status2] + 'Table'[Status3] + 'Table'[Status4]
    ),
    [Cumulative Count]
)

I have attached the pbix file for this example below, hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@Anonymous 

Create measures:

Cumulative Count = 
CALCULATE(
SUM('YourTable'[Count]), // Replace 'YourTable' with your actual table name
FILTER(
ALL('YourTable'[Year-Mon]), // Adjust to your date column
'YourTable'[Year-Mon] <= MAX('YourTable'[Year-Mon])
)
)

Running Rate = 
VAR CumulativeCount = [Cumulative Count]
VAR PreviousCumulativeCount = 
CALCULATE(
SUM('YourTable'[Count]),
FILTER(
ALL('YourTable'[Year-Mon]),
'YourTable'[Year-Mon] < MAX('YourTable'[Year-Mon])
)
)
RETURN
IF(
CumulativeCount = 0,
0,
DIVIDE(CumulativeCount, PreviousCumulativeCount + CumulativeCount, 0)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Hello Kedar,

Thanks for sample code. Here is the latest update

============It is working correct
Cumulative Count = CALCULATE(
SUM('Table1'[Count]),
FILTER (
ALL ('Table1'[YearMonthSorting]), 'Table1'[YearMonthSorting] <= MAX ('Table1'[YearMonthSorting])))

================================== This one is not correct, running rate info giving incorrect result
Running Rate =
VAR CumulativeCount = [Cumulative Count]
VAR PreviousCumulativeCount =
CALCULATE(
SUM('Table1'[Count]),
FILTER(
ALL('Table1'[YearMonthSorting]),
'Table1'[YearMonthSorting] < MAX('Table1'[YearMonthSorting])
)
)
RETURN
IF(
CumulativeCount = 0,
0,
DIVIDE(CumulativeCount, PreviousCumulativeCount + CumulativeCount , 0)
)