Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Ageing

Hi,

 

I'm trying to calcualte ageing of a tickets created which is 6 months old. I don't know how to segregate the data based on the "create date" and "last update date".

 

I've created ageing of the tickets day-wise, I need to segregate them month-wise using the age of the ticket.

 

Any help on this would be appreciated!!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

If it is just a snapshot of the current moment then I would create a calculated column:

daysOld = DATEDIFF(table[Created Date], TODAY(), DAY)

And then you can create another calculated column for the categories:

daysOldCategory = 
IF(table[daysOld] < 15; "Less than 15 days"; 
IF(table[daysOld] <= 30; "15-30 days"; 
IF(table[daysOld] <= 60; "30-60 days"; "60 days and above")))

View solution in original post

5 REPLIES 5
shabirAhmad
Helper II
Helper II

Hi, Can someone tell i have same requirements but reverse of this requirement. I just want to show data filter from number of days as parameters, But the report will not show past date it will show only future date. Let say our item is expiry date is after 30 days as i enter 30 days the report will show those items which will be expired withing 30 days. in the same manner if i enter 60 days the report shows those items will be expired within 60 days etc.

 

 

Best Regards,

Shabir Ahmad

jdbuchanan71
Super User
Super User

@Anonymous 

Are you trying to add the aging buckets as a calculated column on your ticket table?  Can you share a sample of your data that can be copy and pasted easily?  (paste the sample from excel, not a picture of the data).  If you put the sample data together in a table in your .pbix then export it to .csv you can copy and paste from the data.csv file.

 

 

Anonymous
Not applicable

@jdbuchanan71 Yes, I'm trying to create calculated columns. I've these following columns which I'm trying to calculate:

  1. Ticket ID
  2. Created Date

I need to find the ageing of the tickets based on the following category:

  1. 15-30 days
  2. 30-60 days
  3. 60 and above days

Hello @Anonymous 

Give this a try.

Aging Bucket =
VAR DaysAge =
    DATEDIFF ( Table[Created Date], TODAY (), DAY )
RETURN
    SWITCH (
        TRUE (),
        DaysAge < 15, "0-14 days",
        DaysAge < 30, "15-29 days",
        DaysAge < 60, "30-59 days",
        "60+ days"
    )
Anonymous
Not applicable

If it is just a snapshot of the current moment then I would create a calculated column:

daysOld = DATEDIFF(table[Created Date], TODAY(), DAY)

And then you can create another calculated column for the categories:

daysOldCategory = 
IF(table[daysOld] < 15; "Less than 15 days"; 
IF(table[daysOld] <= 30; "15-30 days"; 
IF(table[daysOld] <= 60; "30-60 days"; "60 days and above")))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.