Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Time between two dates based on criteria

Hello!  I have a table of ticket data where I for each critical ticket need to calculate the time since the last critical ticket was registered. Been trying to solve this for a few hours now withou...
DAX
FreemanZ's avatar
FreemanZ
Super User
3 years ago

Hi Jowh, 

Not sure about your use case, is it something like below:

here comes the code for the calculated column:
Duration =
VAR CurrentDate = 'Table'[Date]
VAR TABLE1 =
    CALCULATETABLE(
        'TABLE',
        FILTER ('Table', 'Table'[Priority]="Yes"),
        FILTER ('Table', 'Table'[Date]>CurrentDate)
    )
VAR MinDate =
    MINX (
         TABLE1,
        'Table'[Date]-CurrentDate)
RETURN
    IF (
        'Table'[Priority]="Yes",
        INT( MinDate)
    )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello and thank you! 

It is something similar but in the reverse order I suppose. Latest date should show the previous max date with Priority if that makes sense. Also presented in hours or minutes but I think I could figure that part out myself in worst case 🙂

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Good Luck!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Not really getting this to work 😞 Tried to change to the following:

       

      Time Since Last Critical = 
VAR CurrentDate = Issues[DATE_CREATED]
VAR TABLE1 =
    CALCULATETABLE(
        'Issues',
        FILTER ('Issues', 'Issues'[ISSUE_PRIORITY]="Critical"),
        FILTER ('Issues', 'Issues'[DATE_CREATED]<CurrentDate)
    )
VAR MinDate =
    MAXX( 
         Issues,
        Issues[DATE_CREATED]-CurrentDate)
RETURN
    IF (
        Issues[ISSUE_PRIORITY]="Critical",
        INT( MinDate),blank()
    )

      I don't really understand the MinDate logic though (tried both MINX and MAXX) 

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        The first argument of MINX shall be the filtered table - Table1, instead of the native table - Issues.

        MINX is to scan Table1 and get the immediate last date tagged "Critical."