Forum Discussion

sjrrkb123's avatar
sjrrkb123
Icon for Helper III rankHelper III
5 years ago
Solved

Limiting 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...
Need Help
  • sjrrkb123's avatar
    sjrrkb123
    5 years ago

    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's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

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.