Forum Discussion

MikePowerBI's avatar
MikePowerBI
Icon for Helper II rankHelper II
4 years ago

Aging Metric per Day Over Time

Hi there,

 

Assuming a dataset has records with open and close dates, I am looking to create a metric calulating the percentage of records close within 365 days (including open records that have been open for less than or equal to 365 days) per day over time. 

 

With the below sample data:

Formulas:

Aging Days = IF('Test Data'[Close Date] = BLANK(), DATEDIFF('Test Data'[Open Date], TODAY(), DAY), DATEDIFF('Test Data'[Open Date], 'Test Data'[Close Date],DAY))
 
Aging = IF('Test Data'[Aging Days] > 365, "Over 365 Days", "Not Over 365 Days")
 
Here are some examples:
- 3/1/2019: Aging = 3/3 = 100% because records A, B, C have been open for less than 365 days
- 10/1/2019: Aging = 6/6 = 100% because records A, E closed within 365 days and records F, G, H, I have been open for less than 365 days (records B, C, D are excluded because they closed before 10/1/2019)
- 9/1/2020: Aging = 3/4 = 75% because record H took longer than 365 days to close, record I has been open for exactly 365 days, and records J, K have been open for less than 365 days
- 10/1/2020: Aging = 2/3 = 67% becuase record I took longer than 365 days to close and records J, K have been open for less than 365 days
- 3/1/2021: Aging = 0/2 = 0% because records J, K have been open for greater than 365 days
 
The final goal is to create a line chart showcasing the aging percentage per day over time. Any help with this would be greatly appreciated! 
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    MikePowerBI , create an independent date table. These two measures should help, when you select a date from date table

     

    Aging Days GT 365 days = countx(filter( addcolumns( summarize(filter('Test Data','Test Data'[Open Date] <= max('Date'[Date]) && (isblank('Test Data'[Close Date]) || 'Test Data'[Close Date] >= max('Date'[Date]) )) , 'Test Data' [Record]), "_1", IF(max('Test Data'[Close Date]) = BLANK(), DATEDIFF(min('Test Data'[Open Date]), TODAY(), DAY), DATEDIFF(,min('Test Data'[Open Date]),min(maxx(allselected('DAte'),'Date'[Date]),Max( 'Test Data'[Close Date],DAY)))) ), [_1]<= 365),[Record])


    Total Open = countrows(summarize(filter('Test Data','Test Data'[Open Date] <= max('Date'[Date]) && (isblank('Test Data'[Close Date]) || 'Test Data'[Close Date] >= max('Date'[Date]) )) , 'Test Data' [Record]))

    • MikePowerBI's avatar
      MikePowerBI
      Icon for Helper II rankHelper II
      4 years ago

      Hi amitchandak thank you for the prompt reply, I appreciate it!

       

      I received an error with the Aging Days GT 365 Days measure saying "too few arguements were passed to the DATEDIFF funtion" if I keep the comma in "DATEDIFF(,MIN" and then I get the error "too few arguements were passed to the DAY funtion" if I remove it. Could you help with this error please?

       

      Additionally, would the aging percentage be calculated by taking Aging Days GT 365 Days/Total Open?