Count Ageing Range

[Zaky]

Hi,

I have a data where it has 2 report date (07/03/2019 and 15/03/2019).

What I want to is to get the count of the Remaining Ticket (992 total tickets) of [Work Order ID] by date of each ageing range for Table 2. As shown in Table 2, the formula that is used to count the Remaining Ticket by ageing range is only count for the date 07/03/2019. I want the expected result it also count for date 15/03/2019.

*Remaining_Tickets =

var FindOtherDate = CALCULATE(MAX('WO Raw Data For Ageing'[Report Date]), FILTER('WO Raw Data for Ageing', 'WO Raw Data For Ageing'[Work Order ID]=EARLIER ('WO Raw Data For Ageing'[Work Order ID])))

return

IF('WO Raw Data For Ageing'[Report Date]<> FindOtherDate && 'WO Raw Data For Ageing'[*CountForDupes]>= 2, "Remaining")

I believe that the formula above is working to count the duplication tickets for Table 1.

Before that I have created a calculated column to count the ticket duplication of [Work Order ID] as follow:

*CountForDupes =

var FindDupes = 'WO Raw Data For Ageing'[Work Order ID]

return

CALCULATE(COUNTROWS('WO Raw Data for Ageing'), ALL('WO Raw Data for Ageing'), FindDupes='WO Raw Data For Ageing'[Work Order ID])

Can anyone help with the correct column formula that suit my requirement?

To get make sense, you may download the raw data and PBI files from here

Thank you in advanced for the help.

Thank you in advanced for the help.

[TeigeGao]

Hi Zaky,

Could you please tell us which kind of Ticket can be counted as “Remaining Ticket” in your environment?

Actually, there is no “Remaining Ticket” at 15/03/2019 if we use the regular *Remaining_Tickets, many Tickets are neither in “New”, “Closed” nor “Remaining” in your environment:

Best Regards,

Teige