topic Re: Limiting Aggregation at Different Date Hierarchy Levels in DAX Commands and Tips

Limiting Aggregation at Different Date Hierarchy Levels

sjrrkb123 — Mon, 25 Oct 2021 21:18:38 GMT

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.

Re: Limiting Aggregation at Different Date Hierarchy Levels

ERD — Mon, 25 Oct 2021 21:11:37 GMT

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.

Re: Limiting Aggregation at Different Date Hierarchy Levels

sjrrkb123 — Mon, 25 Oct 2021 21:27:14 GMT

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.

Re: Limiting Aggregation at Different Date Hierarchy Levels

ERD — Mon, 25 Oct 2021 21:35:56 GMT

sjrrkb123 , use 0 instead of blank ().

Re: Limiting Aggregation at Different Date Hierarchy Levels

sjrrkb123 — Mon, 25 Oct 2021 21:40:33 GMT

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

Re: Limiting Aggregation at Different Date Hierarchy Levels

ERD — Mon, 25 Oct 2021 21:51:07 GMT

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.