Forum Discussion

Faye1901's avatar
Faye1901
Helper I
7 years ago
Solved

Date Diff with > 1 condition

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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



3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Faye1901
      Helper I
      7 years ago

      That works perfectly thank you.