Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
@v-robertq-msft This is perfect, thanks! I will use the same methodology for my full portfolio.
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.
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?
@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.
Proud to be a Super User!
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 😛
@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/Community-Blog/Working-With-Financial-Years-In-Power-BI/ba-p/539970
Proud to be a Super User!
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?
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.
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.
Hi @Ashish_Mathur ,
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. 😉
Hi,
I am not sure of how much i can help but share the download link of the PowerBI file.
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?
Hi,
You may download my PBI file from here.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |