Date Diff with > 1 condition

[Faye1901]

Hi All

I've got a relatively simple DAX issue which is elluding me!

I'm trying to count the number of days in a given "Stage" of a workflow, I can count the days between stages which are complete using the formula below but I need to add a second condition that calculates the difference between the start date and today's date (when a stage is not yet complete). Any ideas?

Time in stage ALL = DATEDIFF('All Workflow Data'[StageEntryDate], 'All Workflow Data'[StageCompletionDate], DAY)
*this works, just need something to say if completion date is blank, then use today's date.

Cheers
Faye

[Anonymous · Accepted solution]

Hey @Faye1901 
You can use a variable.

Time in stage ALL = 
VAR _StageCompletionDate = IF(StageCompletionDate = BLANK(), TODAY(), StageCompletionDate)

RETURN
DATEDIFF('All Workflow Data'[StageEntryDate], _StageCompletionDate, DAY)

Thanks!
A

[Anonymous · Accepted solution]

Hey @Faye1901 
You can use a variable.

Time in stage ALL = 
VAR _StageCompletionDate = IF(StageCompletionDate = BLANK(), TODAY(), StageCompletionDate)

RETURN
DATEDIFF('All Workflow Data'[StageEntryDate], _StageCompletionDate, DAY)

Thanks!
A

[Faye1901 · Accepted solution]

That works perfectly thank you. 

[jdbuchanan71]

Hello @Faye1901 

See if this works for you.

Time in stage ALL =
DATEDIFF (
    'All Workflow Data'[StageEntryDate],
    IF (
        ISBLANK ( 'All Workflow Data'[StageCompletionDate] ),
        TODAY (),
        'All Workflow Data'[StageCompletionDate]
    ),
    DAY
)