Forum Discussion

ishanjain's avatar
ishanjain
Frequent Visitor
6 years ago

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.
ishanjain's avatar
ishanjain
Frequent Visitor
6 years ago

This is the error I get in yellow.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

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 🙂

YouTube, LinkedIn