cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Cumulative run rate calculation

Hi,

I'm just in the process of trying to calculate forward looking forecasting using run rates. As it stands I have 6 months of actuals. I want to add the average monthly run rate on to the future months to the end of the financial year. I currently have the dataset below;

 Month Year Input Value TY TY Actual Cumulative Avg. Monthly Run Rate Run Rate Cumulative May-22 £191,070,479 £191,070,479 £184,264,615 £191,070,479 Jun-22 £185,143,532 £376,214,011 £184,264,615 £376,214,011 Jul-22 £181,002,465 £557,216,476 £184,264,615 £557,216,476 Aug-22 £172,448,922 £729,665,398 £184,264,615 £729,665,398 Sep-22 £192,501,057 £922,166,455 £184,264,615 £922,166,455 Oct-22 £183,421,235 £1,105,587,690 £184,264,615 £1,105,587,690 Nov-22 £184,264,615 £1,289,852,305 Dec-22 £184,264,615 £1,474,116,920 Jan-23 £184,264,615 £1,658,381,535 Feb-23 £184,264,615 £1,842,646,150 Mar-23 £184,264,615 £2,026,910,766 Apr-23 £184,264,615 £2,211,175,381

The numbers in green are the ones I'm trying to populate in the Run Rate cumulative column. Would you happen to know how I can create this measure?

Many thanks

Chris

1 ACCEPTED SOLUTION
Community Support

Hi @Cmoore

You can create a new column with the following DAX instead of using Power Query. Ensure that Month Year column is of Date data type. Demo pbix has been attached at bottom.

``````Run Rate Cumulative =
VAR lastActualMonth =
MAXX (
FILTER ( 'Table', 'Table'[TY Actual Cumulative] <> BLANK () ),
'Table'[Month Year]
)
RETURN
SUMX (
FILTER ( 'Table', 'Table'[Month Year] <= EARLIER ( 'Table'[Month Year] ) ),
'Table'[Input Value TY]
)
+ IF (
ISBLANK ( 'Table'[Input Value TY] ),
SUMX (
FILTER (
'Table',
'Table'[Month Year] > lastActualMonth
&& 'Table'[Month Year] <= EARLIER ( 'Table'[Month Year] )
),
'Table'[Avg. Monthly Run Rate]
)
)
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

2 REPLIES 2
Community Support

Hi @Cmoore

You can create a new column with the following DAX instead of using Power Query. Ensure that Month Year column is of Date data type. Demo pbix has been attached at bottom.

``````Run Rate Cumulative =
VAR lastActualMonth =
MAXX (
FILTER ( 'Table', 'Table'[TY Actual Cumulative] <> BLANK () ),
'Table'[Month Year]
)
RETURN
SUMX (
FILTER ( 'Table', 'Table'[Month Year] <= EARLIER ( 'Table'[Month Year] ) ),
'Table'[Input Value TY]
)
+ IF (
ISBLANK ( 'Table'[Input Value TY] ),
SUMX (
FILTER (
'Table',
'Table'[Month Year] > lastActualMonth
&& 'Table'[Month Year] <= EARLIER ( 'Table'[Month Year] )
),
'Table'[Avg. Monthly Run Rate]
)
)
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor