Show Datediff value on today's date instead of the last record

[Anonymous]

Hi all,

I have a table with several records for each single candidate to track what steps they did in a process and the date in which they entered that step.

I want to see in a line chart how many days a candidate has either 1) been in a specific step (X) and are waiting an approval to move next OR 2)  have moved forward (from X to X+1). If 1), then they are considered pending and the diff of how many days should be done between the date of X and today, and the difference should be displayed for such candidate on today's date.

I created the visual, the time spent is calculated correctly as DIFF, but the DIFF value is shown in X instead of Today , and that's because the last record avaialbe for a candidate is for that pending step dates in November.

Be aware: The visual is working fine for case  2), but not for case 1)

Below some examples of candidates in pending steps which is equal to the last step they have done so far

How can I show the DIFF days of these candidates in todays date instead of November?

Be aware that this visual is working fine for the case 2)

[amitchandak]

@Anonymous . a new column

= datediff([updated_date], today(), day)

[Anonymous]

Hi @amitchandak  and thanks for replying,

At the moment I already have a diff formula in place, which is working in returning the difference value. The problem is this part of the sentence: 

1), then they are considered pending and the diff of how many days should be done between the date of X and today, and the difference should be displayed for such candidate on today's date.

Atm the difference is computed as mentioned but you, but the value is returned on the updated date instead of today's date

Here is the formula I am using, which 

DATEDIFF_OffSub-Offer =

var DateOffSub= MAXX(

    FILTER(LevOpportunity_stageChanges, LevOpportunity_stageChanges[toStageId] = "0490c6eb-d3c0-4a13-9e4c-2769e4d38770" && EARLIER(LevOpportunity_stageChanges[mainId])= LevOpportunity_stageChanges[mainId]),

    LevOpportunity_stageChanges[updatedAt])

var DateOffer=    MAXX(

    FILTER(LevOpportunity_stageChanges, LevOpportunity_stageChanges[toStageId] = "1afee169-6a11-4e0f-9ebd-5a01c8df3f1e" && EARLIER(LevOpportunity_stageChanges[mainId])= LevOpportunity_stageChanges[mainId]),

    LevOpportunity_stageChanges[updatedAt])

//If the candidate is still open, I make the difference between the last update and today, but this returns the value on the updated date, and not on today

 var DateDifference= IF(

    ISBLANK(DATEDIFF(DateOffSub,DateOffer,DAY)),

    DATEDIFF(DateOffSub,TODAY(),DAY),

    DATEDIFF(DateOffSub,DateOffer,DAY))

return

DateDifference