Limiting Aggregation at Different Date Hierarchy Levels

[sjrrkb123]

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.

[sjrrkb123 · Accepted solution]

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.

[ERD]

@sjrrkb123 ,

I don't know the particularities of your data model, couldn't reproduce the issue. Version with blank():

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[ERD]

@sjrrkb123 , use 0 instead of blank ().

[sjrrkb123]

@ERD 
Unfortunately that gives blanks for every period in the calendar table I am using rather than just the intervening periods. 

[ERD]

@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.