cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dandel
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:

Dandel_0-1611937252282.png

 

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
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1612419377498.png

 

v-robertq-msft_1-1612419377547.png

 

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.

View solution in original post

14 REPLIES 14
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1612419377498.png

 

v-robertq-msft_1-1612419377547.png

 

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.

View solution in original post

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

v-robertq-msft
Community Support
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:

v-robertq-msft_0-1612342624476.png

 

 

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.

Hi @v-robertq-msft 

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:

Dandel_0-1612355178271.png

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?

negi007
Super User II
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

 

negi007_0-1612016141144.png

 

then create the measure like below

negi007_1-1612016216043.png

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

 

negi007_2-1612016301818.png

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


Dandel
Frequent Visitor

@negi007 

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:

Dandel_0-1612042586756.png

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):

Dandel_1-1612043103918.png

 

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:

Dandel_2-1612043460032.png

 

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 😛

 

 

@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...


https://community.powerbi.com/t5/Community-Blog/Working-With-Financial-Years-In-Power-BI/ba-p/539970

 




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


Proud to be a Super User!

Follow me here


Dandel
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

Dandel_0-1612107160259.png

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:

Dandel_1-1612107191688.png

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

Dandel_2-1612107217533.png

 

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

Or is there a better way?

 

Dandel
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.

Dandel_0-1612104604030.png

 

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/

Hi @Ashish_Mathur ,

 

the formulas in excel are as per described below:

For annual budget trend:

Dandel_1-1612105301775.png

 

April 2020 = Annual budget/12

May 2020 = Annual budget/12 + April 2020

Jun 2020 = Annual budget/12 + May 2020

... until Mar 2021

 

For actuals:

Dandel_0-1612105221489.png

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. 😉

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/

Hi @Ashish_Mathur and @negi007 


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

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Users online (1,406)