Forum Discussion
sjrrkb123
Helper III
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 o...
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.
sjrrkb123Helper III
Helper IIIBetween 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.
