Max Duration - Two Columns - By Category

DAX DAX Calculations
1

Hi Team,

I need some help calculating the Max Duration Based on Category, in this case a Project ref number.

To explain a little more I have an Actual Start Date Column and End Column that I’m tracking Per Month. I would like the Max duration to calculated for that Project ref, in the pictures case 22 March 2022 - 15 April 2020 = 706 days, if its not the max duration for that Project ref is would be left Blank.

Max Duration by Category
Max Duration by Category936×148 43.5 KB

I have attached the working file if it helps. SCF4 RIBA STAGE STUDY.pbix (242.1 KB)

Many thanks, DNA team.

Adam

2

Since Posting I have managed to Calculate the Durations using the Measure Below:

Duration
Duration1151×201 31.6 KB

However, showing only max value and leaving the rest blank still pervades me.

Max Duration Only
Max Duration Only1053×162 51.2 KB

Many Thanks

3

@adam
Variables are actually constants, once you define them their values are fixed, you can’t update the value in any way. That MAXX isn’t doing anything useful, Results that you are getting are MAX values of the table in the filter context modified by ALLEXCEPT but calling variable evaluate outside CALCULATE in a row context in this scenario isn’t useful.

4

Hi @adam

Try this

Duration =
VAR _Actual =
    SELECTEDVALUE ( 'SCF4 PRE-CON-MSC-TIME'[Actual Pre-Construction Start] )
VAR _Project =
    SELECTEDVALUE ( 'SCF4 PRE-CON-MSC-TIME'[Project] )
VAR _CurrentEnd =
    SELECTEDVALUE ( 'SCF4 PRE-CON-MSC-TIME'[Anticipated Preconstruction end date] )
VAR _MaxDate =
    CALCULATE (
        MAX ( 'SCF4 PRE-CON-MSC-TIME'[Anticipated Preconstruction end date] ),
        ALL ( 'SCF4 PRE-CON-MSC-TIME' ),
        'SCF4 PRE-CON-MSC-TIME'[Project] = _Project
    )
VAR _Diff =
    DATEDIFF ( _Actual, _MaxDate, DAY )
RETURN
    IF ( _CurrentEnd = _MaxDate, _Diff, BLANK () )

duration
duration1289×692 188 KB

2 Likes
5

@jbressan

Thanks mate!

Awesome work, very much appreciated.

Kind regards

Adam