cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors