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.
I have sales forecast data for upcoming projects in the following format:
Project | Revenue | Start Date | End Date |
A | $ 16,000 | 11/1/2016 | 12/30/2016 |
B | $ 20,000 | 12/5/2016 | 3/31/2017 |
C | $ 10,000 | 11/15/2016 | 12/30/2016 |
D | $ 12,000 | 1/2/2017 | 3/31/2017 |
I'm looking for the best way in PowerBI to map this to expected revenue each month. Basic assumption would be that the revenue received will be constant over time (so if a project is for $100,000 and extends over 5 months, each month should show $20,000) - this is imperfect vs. reality but "good enough" for my business purpose.
I have an existing Excel template that does this with some rather nasty formulas in a grid so it looks like this:
Period | 1 | 2 | 3 | |||
Start | 1/1/2017 | 2/1/2017 | 3/1/2017 | |||
End | 1/31/2017 | 2/28/2017 | 3/31/2017 | |||
Project | Start | End | Revenue | 1 | 2 | 3 |
A | 11/1/2016 | 12/30/2016 | $ 1,000 | $ - | $ - | $ - |
B | 12/5/2016 | 3/31/2017 | $ 12,500 | $ 3,312 | $ 2,991 | $ 3,312 |
C | 11/15/2016 | 12/30/2016 | $ 1,400 | $ - | $ - | $ - |
D | 1/2/2017 | 3/31/2017 | $ 6,000 | $ 2,022 | $ 1,888 | $ 2,090 |
The formula in each revenue cell above is basically:
=(MAX(MIN([project end],[period end])-MAX($[project start],[period end])+1,0))*$[Revenue $ / day]
So I'm looking for the best (easier?) way to do this kind of report in PowerBI. Preferably then going beyond my table above into some actual bar graphs / etc.
Suggestions / pointers appreciated!
-Rob
Hi @rneppell,
From the Excel formula you provided, it seems that [Revenue $ / day] is a column, but from your sample data, I can't find this column. In your scenario, I would suggest you clarify the formula how to calculate and get values in columns 1,2,3. It would be better if you could share the exact sample data and expected results for our analysis.
Best Regards,
Qiuyun Yu
Apologies; I was trying to simplify & remove extraneous info, but went a bit too far. Let's try again; here's the example:
And here's the actual .xlsx file: https://1drv.ms/x/s!AvkHe_7uKU1SgdJIYBe59136yEPeeg
So the formula in cell G7 which calculates the revenue for Project B over the period 1/1/2017 - 1/31/2017 for example is:
=(MAX(MIN($C7,G$3)-MAX($B7,G$2)+1,0))*$F7
I assume I could learn how to directly replicate this method in PowerBI --- but what I'm really interested in is if there is a *better* way to create this kind of aggregated forecast across time periods.
Thanks in advance,
Rob
...no thoughts at all, folks?
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 |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |