Hi folks, I am new to DAX. I am trying to build a KPI/Measure that will arrive at the net budget at the start of a period/ date selection and utilize this figure in a calculation.
Below is a brief explanation of what the measure is expected to do, and the issue I am running into. At the bottom of this post are tables of sample data, with a computational breakdown provided via excel.
Defining the Measure/Challenge:
I would like a measure that can tell me if the current hours logged by an employee are billable - 'AKA' covered by the NET budget at the start of that period.
For example: We have a total budget of 100. In 2019 we have 20 of actuals / utilization. We are left with a net budget of 80 and our hours are 100% billable (fully covered by the budget). If in 2020 we utilize 100, then only 80% of this is billable.
The primary issue I am having is in calculating the net starting budget (aka that for the 2020 calculation, the net starting budget is 80), although I would appreciate help drafting the whole measure.
This measure will have a few components:
1. Find actuals for the period.
2. Find the net total budget at the beginning of this period (This is where I am struggling).
3. Return a value: If the current period actuals are < the starting net budget, return 100%, if there is 0 or negative starting net budget return 0%, if the current period actuals are > the starting net budget subtract these two figures and divide by actuals to find the % of actuals covered by budget.
Example data:
Budget Data
| Project Name | Trade Name | Budget Quantity |
| Project 1 | Developer | 20 |
| Project 2 | Designer | 25 |
| Project 2 | Developer | 40 |
Actual Data
| Project Name | Trade Name | Actual Quantity | Post Date |
| Project 1 | Developer | 10 | 2019-11-02 |
| Project 1 | Developer | 13 | 2021-10-15 |
| Project 1 | Developer | 20 | 2020-11-03 |
| Project 1 | Developer | 15 | 2021-02-10 |
| Project 2 | Designer | 16 | 2020-05-04 |
| Project 2 | Developer | 4 | 2022-02-19 |
| Project 2 | Designer | 15 | 2019-09-11 |
| Project 2 | Designer | 1 | 2021-04-18 |
| Project 2 | Developer | 20 | 2019-04-02 |
Example Calculation (The rows here are broken out to show how to arrive at the final measure, which is split into total budget, net starting budget, and the ratio).
Hierarchy shown annually:
| Developer | 2019 | 2020 | 2021 | 2022 |
| Actual | 30 | 20 | 28 | 4 |
| Budget | 60 | 60 | 60 | 60 |
| Net Starting Budget | 60 | 30 | 10 | -18 |
| Ratio/Measure | 100% | 100% | 36% | 0% |
Hierarchy shown monthly, within a year (drilling down)
| 2021/Developer | January | February | March | April | May | June | July | August | September | October | November | December |
| Actual | 0 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | 0 | 0 |
| Total Budget | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 |
| Net Starting Budget | 10 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -18 | -18 | -18 |
| Ratio/Measure | 0% | 67% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% |
