Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi folks,
I'm pretty new to Power BI, and currently I am doing long term capacity planning for my job.
Today everything is done via Excel and that's does the trick for the most part. However, having the information in Power BI being able to select forecasts and short term finance targets, quickly makes Excel inferior.
I'm kinda puzzled on how to solve this - So hope someone can help me out here:
My objective is to be able to select, on the fly, various forecasts, and compare to my total yearly supply, hold up against my maximum network utilisation rate, to ultimately calculate my required additional capacity.
I have a database with a 10-year forecasts and a column with a unique forecast name. I need the user to be able to select only one here which is used for calculations. Let's call this primForecast. The forecasts are at least updated every year, with occasional additional updates coming throughout the year. It starts with current year + 10 years ahead. E.g. 2022 to 2031, next year it will be 2023 to 2031 etc.
Furthermore, I have a table with short term financial target settings, where I need the user to select one if there is a target available. Otherwise selection is just none (N/A). This will be used for calculations as well. Let's call this targetForecast. The target forecasts are typically 3-year targets. E.g. from 2022 to 2025, and next year it will be from 2023 to 2026 etc.
Lastly, I want the user to be able to display one or more of the 10-year forecasts on the visualisation, but only as line visuals without being used as calculations forecasts. Naturally, if this is feasible, the user should not be able to select the same forecast as the one being selected in primForecast. Potentially, this slicer would be reset everytime a primForecast is altered.
Let's make an example of how the table would like like (Excel format):
If a targetForecast i selected, and it has values, those values for those specific years, are used within the measure to calculate my additional required capacity. Otherwise if there is no values for the given year, the selected primForecast is used.
To calculate my additional required supply that is done like this:
(demand / maximum utilisation rate (%)) - total supply
If the additional required supply is negative (so not needed), I simply set it to a N/A value.
As mentioned above, I need a slicer where the user can select additional forecasts to be showcased on the visualisation, but not used as a calculation for the required capacity - and it cannot be the same as the primForecast selected.
An example of the below:
So what would be the smartest setup to do this within PowerBI and how to start? I suppose I need to make some measures that can handle this conditionality in the slicers, and furthermore to calculate the additional required supply?
I'm thinking about setting all of this up within a Datamart, so that my users do not need to setup these measures in their reports themselves, but this is a part of the datamodel in the dataset.
Thanks in advance for any guidance!
Hi @CasperSeve,
Did these records really include in your table? If that is the case, you can use calculation expression can extract the current date as a variable to filter and calculate the corresponding date ranges.
You can use the aggregate function(min, max) to get the current date, then use the if statement to check the start end date range based on the current date.
Notice: if you want to use a slicer to achieve the selector effect instead of the filter, you need to use a disconnected table as the source of the slicer.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |