Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Faye1901
Helper I
Helper I

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
 
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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



View solution in original post

That works perfectly thank you. 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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



That works perfectly thank you. 

jdbuchanan71
Super User
Super User

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.