Tremendous mess with dates, I need help

[Syndicate_Admin]

I feel the title is not very descriptive, but I didn't know what to put. Let me explain.

I have a table of 200 rows, and each row is a ticket with a unique reference.

The columns that compose them are

| Reference: the ticket reference.

| Last Updated Date: The last date on which any changes were made to the ticket. The month of this date will always coincide with the study date of the report (this is the one we use to generate the CSV).

| Start date: the date the ticket was opened, it can be dates from the month under study or earlier.

| Resolved Date: The date when an agent terminated the ticket.

| Date Closed: The date on which it is terminated.

The data obtained depends on the "Last Updated Date". That is, although there are tickets with "Start date" months of 2023 and January 2024, the "Last updated date" column will only have dates from the month that is being studied (I am currently with the CSV that has been generated at the end of January).

Among other KPIs, I need to get the number of tickets (rows) that have as "Resolved Date" a date earlier than the report.

I've been trying everything for two days but I can't do it.

The last thing I tried is to create a measure using the MINX funion, to get the minimum date from the "Last updated date" column in order to get the smallest date from that column, in this case 01/01/2024.

Report MIN Date = MINX('iTop Data- CSV', DATEVALUE('iTop Data - CSV'[Last Updated Date]))

Then, I created this other measure to get the rows that have a lower date than the other measure I created.

Measure = CALCULATE(COUNTROWS(

FILTER('iTop Data - CSV','iTop Data - CSV'[Date resolved] < [MIN Report Date])))

I don't know what else I can do, I hope you can help me.

Thank you!

My PBI version is: 2.124.2028.0 64-bit (December 2023)

[Syndicate_Admin]

I've figured it out. In the end what I did is bring in the minimum date using PowerQuery, compare the date of each column with the minimum date column (which matches the report) and create another one that tells me if it's in or out. With that, I've taken it for granted. It took me three days...

Thanks same here!

[Anonymous]

Hi @Syndicate_Admin ,

Can you give us your expected results and sample data, Thank you.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi, I don't know exactly what you're asking me for.

Is it worth it if I leave you the .pbix file and the Excel where the data is?

PBIX File

Excel file with data

Specifically, what I need is the KPI called "Closed Cars". This KPI should result in the number 68 instead of what it currently shows, 86. As you can see, the formula used is the one I mentioned when opening this thread.

Car Locks = CALCULATE(

COUNTROWS(

FILTER(

'iTop Data - CSV', -- This is the table

'iTop Data - CSV'[State] = "Closed"

&&

'iTop Data - CSV'[Resolution date] < [MIN Report Date]

)))

I think what's failing is the MIN Report Date formula.

Thank you in advance and hopefully you can help me with this headache.