Forum Discussion
Helper IIILimiting Aggregation at Different Date Hierarchy Levels
Problem Statement: How to limit the number of values aggregated at a specific date. This date could be calendar date, years, quarters, or months.
Background: I work in a consulting company and our projects with clients are revision based in that on the same project, when the client wants more work, we add a revision rather than starting a new project. So a project could get 50 hours of work in January as revision 0 and another 100 hours of work in May and that would be revision 1.
Sample Data
| Project Code | Year | Quarter - Year | Month Year | Revision Number | Planned Hours | |||||
1A1 | 2021 | 2021-04 | 2021 October | 6 | 300 | |||||
| 1A1 | 2021 | 2021-04 | 2021 October | 5 | 275 | |||||
| 1A1 | 2021 | 2021-03 | 2021 September | 4 | 150 | |||||
| 1A1 | 2021 | 2021-03 | 2021 August | 3 | 150 | |||||
1A1 | 2021 | 2021-03 | 2021 August | 2 | 100 | |||||
| 1A1 | 2021 | 2021-02 | 2021 April | 1 | 50 | |||||
| 1A1 | 2021 | 2021-02 | 2021 April | 0 | 50 |
Desired Output:
Case 1: Only Years visible
| Project Code | Year | Planned Hours | ||
| 1A1 | 2021 | 300 |
Case 2: Quarter Year visible
| Project Code | Quarter - Year | Planned Hours | ||
| 1A1 | 2021-04 |
| 300 | |
| 1A1 | 2021-03 | 150 | ||
| 1A1 | 2021-02 | 50 |
Case 3: Month Year visible
| Project Code | Month - Year | Planned Hours | ||
| 1A1 | 2021 October | 300 | ||
| 1A1 | 2021 September | 150 | ||
| 1A1 | 2021 August | 150 | ||
| 1A1 | 2021 April | 50 |
Caveat: the heirarchy values that will be used will be in a bar chart and either years, quarter - years, or month - years will be on the x-axis and this will be used in a measure, not a calculated column.
I am at a loss how to limit the aggregation like this and would appreciate some assistance.
Between my posting this and now I came up with a great start that uses what you have but also incorporates a disconnected revision number table.
The disconnected table is:Revision Number Table = DISTINCT('Table'[Revision Number])MaxPH = var max_rev = MAX('Table'[Revision Number]) var slns = CALCULATE(SUM('Table'[Planned Hours]), 'Table'[Revision Number] = max_rev) RETURN SWITCH(SELECTEDVALUE('Revision Number Table'[Revision Number]), max_rev, slns, blank())
the only issue now is to show the intervening dates as having 0 planned hours.Basically to have months like 2021 November show as 0 hours.
5 Replies
- ERD
Community Champion
sjrrkb123 , so you need to define most recent planned hours vesrion for defined period (either years, quarter - years, or month - years) ?
You can use a measure:
MaxPH = VAR mostRecentProjectCode = MAX ( 'Table'[Revision Number] ) RETURN CALCULATE ( MAX ( 'Table'[Planned Hours] ), 'Table'[Revision Number] = mostRecentProjectCode )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
sjrrkb123Between my posting this and now I came up with a great start that uses what you have but also incorporates a disconnected revision number table.
The disconnected table is:Revision Number Table = DISTINCT('Table'[Revision Number])MaxPH = var max_rev = MAX('Table'[Revision Number]) var slns = CALCULATE(SUM('Table'[Planned Hours]), 'Table'[Revision Number] = max_rev) RETURN SWITCH(SELECTEDVALUE('Revision Number Table'[Revision Number]), max_rev, slns, blank())
the only issue now is to show the intervening dates as having 0 planned hours.Basically to have months like 2021 November show as 0 hours.
