cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Create a monthly trend from an annual budget

Hi All,

I am trying to create in Power BI something that I have created in Excel, i.e. calculate a monthly trend for an annual budget.

The result I want to achieve is effectively this:

Current solution:

Excel tables per month where

for annual budget montlhy trend I have:

April 2020 = Annual budget/12

May 2020 = Annual budget/12 + April 2020

Jun 2020 = Annual budget/12 + May 2020

... until Mar 2021

I have an annual FY budget broken down by investment. Annual FY budget is not broken down in months.
Each investment has actuals and forecast per month (April 2020 to Mar 2021).

Can someone else in creating either the measure of column/table to enable the annual FY budget to be broken down by month as per example above?

thanks in advance!

1 ACCEPTED SOLUTION
Community Support

Hi, @Dandel

According to your description, I can now understand clearly the logic of your [actual trend value], then I changed my DAX formulas, now you can create these calculated columns to calculate the [actual trend]:

``````rank =

RANKX(FILTER(ALLSELECTED('Table1'),[investment]=EARLIER([investment])),[Month],,ASC,Dense)``````
``````actual trend value =

var _sum=

CALCULATE(SUM([actuals]),FILTER('Table1',[investment]=EARLIER([investment])))

var _lastrank=

CALCULATE(MAX([rank]),FILTER(ALLSELECTED('Table1'),Table1[actuals]<>BLANK()&&[investment]=EARLIER([investment])))

return

DIVIDE(_sum,_lastrank)``````
``````actuals trend1 =

CALCULATE(

SUM('Table1'[actual trend value]),

FILTER(ALLSELECTED(Table1),[Month]<=EARLIER([Month])&&

[investment]=EARLIER([investment])))``````

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

14 REPLIES 14
Community Support

Hi, @Dandel

According to your description, I can now understand clearly the logic of your [actual trend value], then I changed my DAX formulas, now you can create these calculated columns to calculate the [actual trend]:

``````rank =

RANKX(FILTER(ALLSELECTED('Table1'),[investment]=EARLIER([investment])),[Month],,ASC,Dense)``````
``````actual trend value =

var _sum=

CALCULATE(SUM([actuals]),FILTER('Table1',[investment]=EARLIER([investment])))

var _lastrank=

CALCULATE(MAX([rank]),FILTER(ALLSELECTED('Table1'),Table1[actuals]<>BLANK()&&[investment]=EARLIER([investment])))

return

DIVIDE(_sum,_lastrank)``````
``````actuals trend1 =

CALCULATE(

SUM('Table1'[actual trend value]),

FILTER(ALLSELECTED(Table1),[Month]<=EARLIER([Month])&&

[investment]=EARLIER([investment])))``````

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

Frequent Visitor

@v-robertq-msft This is perfect, thanks! I will use the same methodology for my full portfolio.

Community Support

Hi, @Dandel

According to your description and formulas, I created some calculated columns to achieve your requirement, you can check if they are what you wanted:

I created these calculated columns to calculate the annual budget trend:

``````annual trend value =

DIVIDE([annual budget],12)``````
``````annual budget trend1 =

CALCULATE(

SUM('Table1'[annual trend value]),

FILTER(ALLSELECTED(Table1),[Month]<=EARLIER([Month])&&

[investment]=EARLIER([investment])))``````

I created these calculated columns to calculate the actuals trend:

``````rank =

RANKX(FILTER(ALLSELECTED('Table1'),[investment]=EARLIER([investment])),[Month],,ASC,Dense)``````
``````actual trend value =

var _sum=

CALCULATE(SUM([actuals]),FILTER('Table1',[Month]>=DATE(YEAR(EARLIER([Month])),1,1)&&[Month]<=EARLIER([Month])&&[investment]=EARLIER([investment])))

return

DIVIDE(_sum,[rank])``````
``````actuals trend1 =

CALCULATE(

SUM('Table1'[actual trend value]),

FILTER(ALLSELECTED(Table1),[Month]<=EARLIER([Month])&&

[investment]=EARLIER([investment])))``````

Then I created a line chart and a Slicer like this:

And I guess this is what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

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

Frequent Visitor

for annual budget its spot on, thanks!

For actuals trend its still not there. If you plot in your file my original line and yours you can see the mismatch:

looking at the calculations I believe its because in your calculated column "actual trend value" you are effectively dividing:

April/rank for April

March/rank for March

and so on.

In my calculation it should be:

(Sum of All months with actuals)/(rank of last month with actuals)

for your calculated column "actual trend value", April for investment "a" would be:

(0.2+0.2+0.6+0.4)/(4)=0.35

does it make sense?

Super User II

@Dandel You can create running total measures to have cumulative trend line of your actuals and budgets.

right click on measure and select new quick measure

then create the measure like below

follow the above steps to calculate both acutal and budget running total then you can use them in your charts like below

i hope above steps helps you in achieving you wish to do.

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me here

Frequent Visitor

Thanks for the suggestion.

Unfortunately I don't think the solution for my case is that simple. This is because I don't have my annual budget broken down by dates.

Here is a sample of my tables:

My annual budget is broken down by investment ID but no date because its annual:

Now I want to get the sum of all of these investments and distribute it over the FY (April to March) evenly, i.e. each month is the sum divided by the number of months (12) plus the value of the previous month (to give that cumulative view). By the 12th month I should get to the full sum of the table above.

For actuals its something similar but in that case my raw data gives me a blend between actuals and forecasts. i.e. I would have the months and the figures would be actuals until the month stated in the file name (this case December) and everything else would be the forecast of the investment (from December to March):

from this raw data in Excel I create a few more tables. One is just for actuals (everything past December is 0) and then I create a actuals trend which is the full sum of actuals divided by the number of months with actual figures - in this case 9, i.e April to December).

In Power BI I turn them into this:

So quite more complex than the simple quick measure I believe.

I'm sure I can recreate what I do in excel in Power BI, just don't know how 😛

Super User II

@Dandel is it possible for you to create one seperate table for our budget table may be in excel then you can import to your data model in powerbi and link it to your date table. Your table could be like below in excel. in date column use first day of month. Once you import this table and link with your excel table, you should be able to have annual budget target in powerbi. if you wish to have budget by investment ID, then you will have to add this column also in below table and against each Investment ID, you will have budget

 Date Annual Budget 1-May-20 17 1-Jun-20 17 1-Jul-20 17 1-Aug-20 17 1-Sep-20 17 1-Oct-20 17 1-Nov-20 17 1-Dec-20 17 1-Jan-21 17 1-Feb-21 17 1-Mar-21 17 1-Apr-21 17

further, if you need any help in creating financial year calendar you may refer to below links

https://community.powerbi.com/t5/Desktop/DAX-Formulas-for-Fiscal-Year-Fiscal-Quarter-and-Month-Short...

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me here

Frequent Visitor

Hi @negi007

My plan is to create the following:

for each investment ID create the columns for the FY I need as per example

My problem is that I still don't know how to populate the above table with those annual budgets.

Then I would unpivot all columns to get the following table:

Once I have the above table I need to still add another column or measure where the result would be something like this:

This last column is the formula I also need help with really.

Or is there a better way?

Frequent Visitor

Yes, I think I will have to add the dates in Power BI (am trying to get way from doing things in excel, if possible).

I already have a dates table with FY info, they're just not associated with my budget as its annual and not monthly.

Super User III

Hi,

One good way to start is to build an MS Excel file with the formulas you would use to solve as though this was an Excel problem.  I will then translate those formulas in the DAX formula language.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

the formulas in excel are as per described below:

For annual budget trend:

April 2020 = Annual budget/12

May 2020 = Annual budget/12 + April 2020

Jun 2020 = Annual budget/12 + May 2020

... until Mar 2021

For actuals:

April 2020 = Total YTD actuals/(count of number of months with actuals)

May 2020 = Total YTD actuals/(count of number of months with actuals) + April 2020

and so on until March 2021

I do these at individual project level so that then I can sum it all up and use filters to see smaller areas of the company in the same graphs. 😉

Super User III

Hi,

I am not sure of how  much i can help but share the download link of the PowerBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

please find below the link to an example of data I could have and the excel calculations.

would you be able to help me achieve the same result but in dax?

https://we.tl/t-pRb02xD3lu

Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Check it Out!

Click here to read more about the April 2021 Updates!

#### Sign up for our May 4th event!

May the fourth be with you, join us online!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors