Calculating date difference between a column and a dynamic date from a user filter

[ccindyp]

Hello,

I have a tickets table as shown below. I need every week to report on the unupdated tickets from the last 2 weeks. 

Example: How many open tickets were not modified by the support team within the last 2 weeks?

It works if I use "today" within the date diff column : 

Unupdated issues from today in weeks =

DATEDIFF('Fact - All tickets'[Changed On], TODAY(),WEEK)

Then combiend in a measure :

04 - Last 2 weeks issues =

CALCULATE(

    [02 - Tickets opened during the period],

    REMOVEFILTERS ('Calendar'),

    FILTER('Fact - All tickets','Fact - All tickets'[Unupdated issues from today in weeks] >= 2),

    USERELATIONSHIP('Calendar'[Date],'Fact - All tickets'[Changed On])

    )

I need to enhance this calculation by making the today dynamic: I have a filter available by week in my dashboard and using this filter the calculation should adjust: If I am referencing week 1 of 24, count number of untouched tickest from 25th dec till 7th jan. If I filter on week 2, system should give me the number of tickets from 1st jan to 14th jan, etc...

I am stuck because my reference is on one hand a column and on the other hand a dynamic filter.

I cannot use any status, because the following week, I need to add the data from the previous week, which means my data source is growing every week, and I can have some duplicate tickets, because of update in status or in update dates.

The measure : [02 - Tickets opened during the period] is referring to events in progress, as I need to know every week how many tickets are opened, and from those open tickets, I should filter on the "not updated in last 2 weeks"

Are you able to help me solve this with DAX or how should I work around this question?

Thank you very much, 

Client ID

Client

Ticket

Ticket Text

Priority

Category

Cat 2

Agent

Reported On Date Time

Reported By

In Process Date Time

Resolved On Date Time

Closing Date Time

Status

Changed On

Changed By

1111111111

Client Name

12345

Ticket description

Urgent

OTHER

Other

John Doe

28.09.2023 08:27:59

Client

30.09.2023 08:31:44

10.11.2023 16:37:26

28.11.2023 16:37:32

Closed

28.11.2023

Test user

1111111111

Client Name

12346

Ticket description

Urgent

OTHER

Other

John Doe

29.09.2023 08:45:59

Client

30.09.2023 08:31:44

#

#

In Process

28.11.2023

Test user

[ccindyp · Accepted solution]

Hello, I did try a few formulas and it seems to work. I am sharing the formulas in case it helps other community members: 

Total tickets =

CALCULATE(

    DISTINCTCOUNT(‘Ticket table’ [Ticket]),

    USERELATIONSHIP('Calendar'[Date], ' Ticket table'[Date Open])

)

--------------------

Open tickets =

VAR EndDatePervisual = MAX('Calendar'[Date])

VAR Result =

CALCULATE(

    [Total tickets],

    REMOVEFILTERS ('Calendar'),

    ‘Ticket table’[Date open]<= EndDatePervisual,

    ‘Ticket table’[Date resolved] > EndDateperVisual

    ||

    ISBLANK(‘Ticket table’[Date resolved])

    )

RETURN

Result

--------------

Total open tickets =

CALCULATE(

    [Open tickets],

    USERELATIONSHIP('Calendar'[Date], ‘Ticket table’[Date open]),

    FILTER('Dim - Status', 'Dim - Status'[Status] = "Open"||'Dim - Status'[Status] = "Processing")

)

----------------

Unupdated issues last 2 weeks =

VAR TwoWeeksAgo = [Week End Date] - 14

RETURN

CALCULATETABLE(

    ROW(

    "Total open tickets", [Total open tickets]

    ),

    KEEPFILTERS( FILTER( ALL( ‘Ticket table’[Changed On] ), ‘Ticket table’[Changed On] <= TwoWeeksAgo )))

There can be other options to reahc the same results, if anybody can share any alternative, I would be happy to hear 🙂

Thanks and have a nice day

[ccindyp]

Hello and thank you very much for quick reply. 

The data is indeed changing dynamically, when I change the week filter. But it is not removing the last 2 weeks update tickets. I have then all my tickets still: example for filter on week 1-2024:

I just realised my original post was not clear enough: I need to know how many open tickets were not updated in the last 2 weeks. So I need to get the number of open tickets in the period - number of tickets updated in the last 2 weeks. That would give me my number of tickets not touched in the last 2 weeks. 

In this example I need to have the number of open tickets from sept (for example) till dec 24th not 27th, 28th, 29th. In this example those tickets are still in the status "open". So even if I add the status filter, it would still show me those ones. 

In excel it is only getting the list of open tickets, and filtering on the "changed on" column, removing the last 2 weeks.

Thank you very much

[123abc]

Thank you for the clarification. To achieve the desired outcome of counting the number of open tickets that have not been updated in the last 2 weeks, you need to modify the DAX measure accordingly.

Here's how you can adjust the measure:

Open Tickets Not Updated Last 2 Weeks =
VAR StartDate = [Dynamic Week Start]
VAR EndDate = [Dynamic Week End]
VAR TwoWeeksAgo = EndDate - 14
RETURN
VAR OpenTickets =
CALCULATE(
COUNTROWS('Fact - All tickets'),
'Fact - All tickets'[Status] = "Open" -- Considering only open tickets
)
VAR UpdatedLast2Weeks =
CALCULATE(
COUNTROWS('Fact - All tickets'),
'Fact - All tickets'[Changed On] >= TwoWeeksAgo &&
'Fact - All tickets'[Changed On] <= EndDate
)
RETURN
OpenTickets - UpdatedLast2Weeks

This measure first calculates the total number of open tickets (OpenTickets). Then, it calculates the number of tickets that have been updated in the last 2 weeks (UpdatedLast2Weeks). Finally, it returns the difference between the total number of open tickets and the number of tickets updated in the last 2 weeks, effectively giving you the count of open tickets that have not been updated in the specified period.

Ensure that you adjust 'Fact - All tickets' with the correct table name containing your ticket data, and 'Fact - All tickets'[Status] and 'Fact - All tickets'[Changed On] reflect the appropriate column names in your dataset.

This measure should provide you with the count of open tickets that have not been updated in the last 2 weeks based on the dynamically selected week filter.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[ccindyp]

Hello and thank you for the time spent.

While I understand the concept, I am not able to replicate the calculation, because:

1-The VAR “[Dynamic Week Start]/[Dynamic Week End]” are not stated. I am trying to replicate same as previous post as the reference date, but in the example you take December 25th as a fixed date, while I would need it to be dynamic.

Reference Date =
VAR SelectedWeekStart = MIN('Calendar'[Date]) // Assuming 'Calendar' is your Date table
RETURN
SelectedWeekStart - MOD(SelectedWeekStart - DATE(2023, 12, 25), 7)

I do have in my calendar table stated a week start date and week end date column, and I have been trying to work my calculations around with theses 2 columns without success. Do you think I can make reuse of my calculated column? How would you do it?

2- Secondly, the VAR “[Dynamic Week Start]” is not referred to anywhere in the calculation, I am not sure why I need it and unsure also how to replicate it (linked to point 1)

3-Thirdly, instead of counting rows, I used various DAX calculations to replicate the “event in progress” pattern:

01 - Total tickets =

CALCULATE(

    DISTINCTCOUNT('Fact - All tickets'[Ticket]),

    USERELATIONSHIP('Calendar'[Date], 'Fact - All tickets'[Reported On])

)

02 - Tickets opened during the period =

VAR EndDatePervisual = MAX('Calendar'[Date])

VAR StartDateperVisual = MIN('Calendar'[Date])

VAR Result =

CALCULATE(

    [01 - Total tickets],

    REMOVEFILTERS ('Calendar'),

    'Fact - All tickets'[Reported On]<= EndDatePervisual,

    'Fact - All tickets'[Resolved On] > StartDateperVisual

    ||

    ISBLANK('Fact - All tickets'[Resolved On])

    )

RETURN

Result

02 - Total open tickets =

CALCULATE(

    [02 - Tickets opened during the period],

    USERELATIONSHIP('Calendar'[Date], 'Fact - All tickets'[Reported On]),

    FILTER('Dim - Status', 'Dim - Status'[Status] = " Open Status 1"||'Dim - Status'[Status] = "Open Status 2"||'Dim - Status'[Status] = " Open Status 3"||'Dim - Status'[Status] = " Open Status 4"||'Dim - Status'[Status] = " Open Status 5")

)

It should return all my tickets opened in week 1 (depending on my filter/slicer) and filter as “open status”.

With this approach I am hoping I can remove the part of the calculation filtered:
“'Fact - All tickets'[Status] = "Open" -- Considering only open tickets”

I guess If I manage to get my reference dates correctly, I could use the scenario you explained to me previously:

"RETURN
OpenTickets - UpdatedLast2Weeks"

Do you see it as an issue with the reference dates or with the events in progress calculations?

Thanks a lot for your support in this matter.

Regards

[123abc]

To achieve your desired outcome of dynamically calculating the number of unupdated tickets from the last 2 weeks based on a user-selected week filter, you can use DAX measures and consider the following approach:

1. Create a Date Table: Ensure you have a Date table in your data model that spans across all the dates in your tickets data.

2. Calculate the Reference Date: Create a measure to determine the reference date based on the selected week. You can use the selected week number to find the starting date of that week. This measure should dynamically adjust based on the user's selection.

3. Calculate the Number of Unupdated Tickets: Use DAX measures to calculate the number of unupdated tickets from the last 2 weeks based on the reference date.

Here's how you can implement this in DAX:

1. Calculate the Reference Date:

Reference Date =
VAR SelectedWeekStart = MIN('Calendar'[Date]) // Assuming 'Calendar' is your Date table
RETURN
SelectedWeekStart - MOD(SelectedWeekStart - DATE(2023, 12, 25), 7)

This measure calculates the start date of the selected week by subtracting the day of the week from the selected date and then subtracting the appropriate number of days to get to the start of the week (in this case, December 25, 2023).

2. Calculate the Number of Unupdated Tickets:

Unupdated Tickets Last 2 Weeks =
VAR ReferenceDate = [Reference Date]
RETURN
CALCULATE(
COUNTROWS('Fact - All tickets'),
FILTER(
'Fact - All tickets',
'Fact - All tickets'[Changed On] <= ReferenceDate &&
'Fact - All tickets'[Changed On] >= ReferenceDate - 14 &&
'Fact - All tickets'[Status] = "In Process" // Adjust as needed
)
)

This measure calculates the count of unupdated tickets within the last 2 weeks based on the reference date. Adjust the filter conditions as per your data model and requirements.

Usage:

• Use the Reference Date measure to dynamically calculate the reference date based on the selected week.
• Use the Unupdated Tickets Last 2 Weeks measure to get the count of unupdated tickets within the last 2 weeks based on the reference date.

Ensure you adjust the table and column names in the DAX measures according to your data model. Additionally, modify the filter conditions based on your ticket statuses and other criteria.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.