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 everyone,
I've created a measure that calculates the cumulative count of hires and terminations in order to calculate the headcount (hires minus terminations):
HiredStaff =
CALCULATE (
COUNT ( 'Table'[EmployeeID] ),
FILTER (
ALLSELECTED( 'Table' ),
OR('Table'[Event] = "Hire" , 'Table'[Event] = "Rehire")
&& 'Table'[RowID] <= MAX ( 'Table'[RowID] )
)
)
TerminatedStaff =
CALCULATE (
COUNT ( 'Table'[EmployeeID] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Event] = "Terminated"
&& 'Table'[RowID] <= MAX ( 'Table'[RowID] )
)
)
The measure [Headcount] is calculated like this: Headcount = [HiredStaff] - [TerminatedStaff]
In my dashboard I have a line chart that shows the development of headcount over time (Axis: EventDate, Values: [Headcount]).
Now I would like to add a second line chart that shows a moving average forecast for headcount for a period of time in the future. I've managed to do it in Alteryx using the Multi-Row-Formula-Tool, but I don't know how to do this in Power BI.
I've created a set of sample data, a sample Alteryx workflow that produces the result I need and a sample Power BI dashboard that you can find on Dropbox: https://www.dropbox.com/sh/2kw6iplladj84a3/AABJ3N6vfeyw-JxEddf2Hvl-a?dl=0 The rows for the years 2022 & 2023 in the Excel and Power BI file are empty because they should be filled with the calculated moving average forecast.
Does anyone have an idea on how to solve the forecast problem?
Thanks a lot in advance.
Best,
Matisse
Solved! Go to Solution.
Hi @Anonymous
Thanks for your reply.
I find the formula it uses involves recursion. However, DAX language doesn't support recursion.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Could you share a sample file for us to find the reason?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
hi @v-xiaotang ,
Thanks for your reply!
For some reason I can't upload files to this forum directly. So I've uploaded them to Dropbox. This is the link:
https://www.dropbox.com/sh/2kw6iplladj84a3/AABJ3N6vfeyw-JxEddf2Hvl-a?dl=0
Following this link you can find an excel spreadsheet with dummy data, a sample Power BI dashboard and a sample Alteryx workflow (all based on the dummy data).
My goal is to create a Moving Average Forecast for the Headcount. It's easy to do this in Alteryx using the Multi-Row-Formula-Tool, but I don't know how to do this in Power BI. The screenshot shows the result in Alteryx, that I would like to re-create in Power BI in order to show it on a chart:
Power BI:
Workflow:
And this is how the Multi-Row-Formula-Tool in Alteryx is configured:
I would be grateful for any idea or hint on how to solve this.
Thanks in advance.
Best,
Matisse
ps: @amitchandak maybe the uploaded sample dashboard & worklfow make my problem and the desired solution clearer than my description in my initial post
Hi @Anonymous
Thanks for your reply.
It seems that in measure [MovAvg_Headcount], the table in filter function is not same as the table in the other 2.
I will continue to find out the exact reason and the correct [MovAvg_Headcount].
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
thanks @v-xiaotang !
I didn't even realize that there is something wrong with the measure [MovAvg_Headcount]. I thought it works, but thanks for letting me know.
[MovAvg_Headcount] calculates the moving average for the headcount (meaning the historic data). Would you also have an idea on how to create another measure (or calculated column) to calculate the moving average forecast for headcount (like what I did in Alteryx)? I actually don't really need the moving average for the historic headcount. I just created the [MovAvg_Headcount] measure, hoping that it would help to calculate the moving average forecast.
Hi @Anonymous
I have not used this software, so not quite understand the formula Alteryx uses to calculate the moving average forecast for headcount. Can you describe how the result you want is calculated? If you do it manually instead of using Alteryx.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
hi @v-xiaotang ,
I've added an excel file (Formula.xlsx) with a formula that calculates the moving average forecast in Excel to the dropbox folder: https://www.dropbox.com/sh/2kw6iplladj84a3/AABJ3N6vfeyw-JxEddf2Hvl-a?dl=0
Here you can see a screenshot of the formula I used in Excel:
This is the formula:
=ROUND(IF(H28="";AVERAGE(IF(H27="";J27;H27);IF(H26="";J26;H26);IF(H25="";J25;H25);IF(H24="";J24;H24);IF(H23="";J23;H23);IF(H22="";J22;H22));"");5)
I would like to recreate a similar formula in Power BI to calculate the moving average forecast in my dashboard. I hope this makes it clearer. If not, please let me know and I'll provide more detailed information.
Best,
Matisse
Hi @Anonymous
Thanks for your reply.
I find the formula it uses involves recursion. However, DAX language doesn't support recursion.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
hi @v-xiaotang ,
does this mean it's impossible to calculate the moving average forecast in Power BI?
I get an error message if I try to open your file:
Seems my Power BI version is outdated. Unfortunately, I cannot update it myself. I need to wait for my company to do this. Is there a way I can solve this?
Best,
Matisse
Hi @Anonymous
Yes, it's impossible to calculate the value involves recursion with DAX. Besides, you can click Close button, then the file will open.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try with some sort group by and date table
CALCULATE(AverageX(Values('Date'[Date]) , Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
refer if needed
@amitchandakThanks for your reply!
I've checked your measure and your post (including your attached dashboard), but unfortunately I don't see the solution for my problem. Could you elaborate more?
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |