Forum Discussion
Datediff in DAX
I have multiple ticket ids of an order, now I want to find out if those ticket ids occur 3 or more times within 30 days. Can anyone suggest? columns are ticket_id and Dispactch_date.
Fowmy
Super User
Super Userishanjain
Add the following code to your table where you have the Ticket ID and the Despatch Date. The new column will show you numbers if the same ticket appeared more than or equal to 3 times within 30 days otherwise zero will appear.
You can download the file: HERE
Over 2 Tickets With 30 Days =
VAR CURRENT_TICKET = [Ticket ID]
VAR CURRENT_DATE = [Despatch Date]
VAR MINDATE =
FIRSTDATE(
FILTER(
VALUES(TICKETS[Despatch Date]),
ABS(DATEDIFF(TICKETS[Despatch Date],CURRENT_DATE,DAY))<=30
)
)
VAR MAXDATE =
LASTDATE(
FILTER(
VALUES(TICKETS[Despatch Date]),
ABS(DATEDIFF(TICKETS[Despatch Date],CURRENT_DATE,DAY))<=30
)
)
VAR TICKTCOUNT =
COUNTROWS(
FILTER(
TICKETS,
TICKETS[Ticket ID]=CURRENT_TICKET &&
TICKETS[Despatch Date]>=MINDATE &&
TICKETS[Despatch Date]<=MAXDATE
)
)
RETURN
IF(
TICKTCOUNT >= 3,
TICKTCOUNT,
0
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
This is the error I get in yellow.
- Fowmy
ishanjain
Try this please:Over 3 Tickets With 30 Days = VAR CURRENT_TICKET = [Ticket ID] VAR CURRENT_DATE = [Despatch Date] VAR MINDATE = MINx( FILTER( VALUES(TICKETS[Despatch Date]), ABS(DATEDIFF(TICKETS[Despatch Date],CURRENT_DATE,DAY))<=30 ), TICKETS[Despatch Date] ) VAR MAXDATE = MAXx( FILTER( VALUES(TICKETS[Despatch Date]), ABS(DATEDIFF(TICKETS[Despatch Date],CURRENT_DATE,DAY))<=30 ), TICKETS[Despatch Date] ) VAR TICKTCOUNT = COUNTROWS( FILTER( TICKETS, TICKETS[Ticket ID]=CURRENT_TICKET && TICKETS[Despatch Date]>=MINDATE && TICKETS[Despatch Date]<=MAXDATE ) ) RETURN IF( TICKTCOUNT >= 3, TICKTCOUNT, 0 )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂