How to get Aging in Hours

[Anonymous]

Hi,

i have here my current formula/logic for aging >> 

IF(Query1[Status]="Completed",BLANK(),NETWORKDAYS(Query1[Due],Query1[Now])*24)+0

but i want to get if the status is "Completed" the 'Aging' should be blank and if the status is "On Hold and In Progress" the aging hours should be reflected.

Status	Date and Time Received	Date and Time Accomplished	Aging Hours
Completed	1/23/2023 18:34	1/23/2023 19:15
In Progress	1/18/2023 14:58
On hold	1/20/2023 16:00

[tamerj1]

Hi @Anonymous 
Not sure what are Query1[Due] and Query1[Now] that exist in your dax code. However, please refer to attached sample file with proposed solution.

Aging Hours = 
VAR CurrentIDTable = CALCULATETABLE ( Query1, ALLEXCEPT ( Query1, Query1[ID] ) )
VAR AccomplishDateTime = MAXX ( CurrentIDTable, Query1[Date and Time Accomplished] ) 
RETURN
    IF (
        Query1[Status] <> "Completed",
        NETWORKDAYS ( Query1[Date and Time Received], AccomplishDateTime ) * 24 + 0
    )

[Anonymous]

Hi @tamerj1 Thank you for this.! this works, however, i want to depend it aswell in SLA.

Please see sample below:

1. if this is still On Hold and In Progress and already beyong due from it's SLA the hours of aging should reflect. if already completed, hours of aging should be blank.

[tamerj1]

@Anonymous 
Please provide complete and clear picture with one or two examples.

[Anonymous]

Hi @tamerj1  First, Thank you for your immediate response.

to better understand, for e.g. once you received the data or request it should get done within 4 hours (SLA) from the date you received and if after 4 hours from it's SLA if the request is not  yet completed the Aging will start to count

1. i want to get the hours of aging that will start from it's supposed 'Due Date'  but not yet completed because it still "On Hold" and "In Progress"

here's the sample below

[tamerj1 · Accepted solution]

@Anonymous 
Still not sure if this is what you're looking for or not

Aging Hours = 
VAR SLA = Query1[SLA (in hours)]
VAR DateReceived = Query1[Date and Time Received]
VAR CurrentIDTable = CALCULATETABLE ( Query1, ALLEXCEPT ( Query1, Query1[ID] ) )
VAR AccomplishDateTime = MAXX ( CurrentIDTable, Query1[Date and Time Accomplished] ) 
VAR Hours = DATEDIFF ( DateReceived, AccomplishDateTime, HOUR )
VAR AgingHours = 24 * ( NETWORKDAYS ( DateReceived, AccomplishDateTime ) )
RETURN
    IF (
        Query1[Status] <> "Completed" && Hours > SLA,
        IF ( 
            Hours < 24,
            Hours,
            AgingHours
        )
    )

[Anonymous]

Hi @tamerj1 Both working but i wanted to get the specific.

1. Date and Time Received, Date and Time Accomplished, Staus, and SLA.

2. if Date and Time Accomplished is Blank or no Data yet, the "Hours of Aging" should start if this is beyond SLA now.

3. and if Both Date and Time Received and Date and Time Accomplished (Status-Completed) is Filled or have data already, the Hours of Aging should be Blank now

[tamerj1]

@Anonymous 
You just need to present input data along with the expected result. Examples explain much better than words. Please do not attempt to over simplify the problem as that might be misleading. Only present the problem as is using sample data along with (an) explanatory example(s).

[Anonymous]

Hi @tamerj1  I hope i can explain this better now. i tred your formula and Logic but i still getting some error/s.

1. In the highlighted Red, this is not what I'm expecting because Date & Time Accomploshed is Blank/No data yet. so, Hours Aging should be reflected.

2. on the 2nd result, which is Highlighted in Yellow, this is CORRECT because Date & Time Received and Date & Time Accomplished is filled so, Hours of Aging should be Blank

3. on the 3rd result, Highlighted in Blue, CORRECT as well since Date & Time Accomplished is Blank so Hours of Aging Should be reflected.

Note: this is still depends on the SLA. if beyond SLA, Hours of Aging should start to count.

Really appreciate your time on this. thank you! 🙂

[tamerj1]

@Anonymous 
Great! How did you calculate 8 and 7?

[Anonymous]

I just copied the results you got. 🙂 

[FreemanZ]

hi @Anonymous 

are you expecting something like:

Aging Hours = 
VAR _hours = 
([Date and Time Accomplished] - [Date and Time Received])*24
RETURN
IF(Query1[Status]="Completed", BLANK(), _hours)

[Anonymous]

Hello, Big thanks for this. but the result shows "10787777.9666667" like this

[FreemanZ]

hi @Anonymous 

it is because you don't have data there:

what do you expect to show in such cases?

[Anonymous]

Hi @FreemanZ Freemanz Right! i have no data yet for "Date Accomplished" that's why i want to see how many hours of Aging for this since it's already beyond SLA and not yet completed/accomplished.

here's the sample below:

1. if this is still On Hold and In Progress and already beyong due from it's SLA the hours of aging should reflect. if already completed, hours of aging should be blank.

[Lamia]

Hello,

You may try this revised DAX

=IF(Query1[Status]="Completed",BLANK(),IF(Query1[Status]="In Progress" or Query1[Status]="On Hold",NETWORKDAYS(Query1[Date and Time Received], NOW())*24,NETWORKDAYS(Query1[Due],Query1[Now])*24))

You will checks the status first, if it's "Completed", then the Aging Hours will be blank.

If the status is "In Progress" or "On Hold", then the Aging Hours will be calculated as the difference between the Date and Time Received and the current date and time, multiplied by 24 (since you want it in hours).

If the status is neither "Completed", "In Progress", nor "On Hold", then the Aging Hours will be calculated as the difference between the Due date and the current date, multiplied by 24.

Hope this helps.

[Anonymous]

Hi @Lamia  I appreicate this. however, I'm getting blank result.. can you please take a look at again on the sample above and see if you can figure out how to fix this. would really appreciate your help on this. thank you!