topic Re: How to get Aging in Hours in DAX Commands and Tips

How to get Aging in Hours

Anonymous — Thu, 02 Feb 2023 14:16:36 GMT

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

Re: How to get Aging in Hours

Anonymous — Thu, 02 Feb 2023 14:40:18 GMT

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.

Re: How to get Aging in Hours

FreemanZ — Thu, 02 Feb 2023 14:44:10 GMT

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)

Re: How to get Aging in Hours

Anonymous — Thu, 02 Feb 2023 17:01:00 GMT

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

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 06:18:43 GMT

Hi Anonymous 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!

Re: How to get Aging in Hours

FreemanZ — Fri, 03 Feb 2023 06:54:14 GMT

hi Anonymous

it is because you don't have data there:

what do you expect to show in such cases?

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 07:10:58 GMT

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.

Re: How to get Aging in Hours

tamerj1 — Fri, 03 Feb 2023 07:24:52 GMT

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 )

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 07:50:38 GMT

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.

Re: How to get Aging in Hours

tamerj1 — Fri, 03 Feb 2023 08:01:19 GMT

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

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 08:12:16 GMT

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

Re: How to get Aging in Hours

tamerj1 — Fri, 03 Feb 2023 09:22:06 GMT

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 ) )

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 10:22:19 GMT

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

Re: How to get Aging in Hours

tamerj1 — Fri, 03 Feb 2023 10:36:32 GMT

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).

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 10:49:26 GMT

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! 🙂

Re: How to get Aging in Hours

tamerj1 — Fri, 03 Feb 2023 11:09:38 GMT

Anonymous
Great! How did you calculate 8 and 7?

Re: How to get Aging in Hours

Anonymous — Fri, 03 Feb 2023 11:15:20 GMT

I just copied the results you got. 🙂