Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Incorrect DAX

Hi All,

 

Please see below data.

 
 

The column "Hire (Days)" is a calculated column created by the below DAX.

 

Hire Duration = DATEDIFF('Standing Scaffold Data'[On Hire Date],'Standing Scaffold Data'[Off Hire Date],DAY)

 

I require the calculated column to show me the hire for all items but at the moment it is not showing me the "Hire (Days)"  for items still on hire. The items that are not yet off-hire will need to show a "Hire (Days)" to the current day.

 

Is there any way I can use DAX to show me the Hire Days for all items?

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • FarhanAhmed's avatar
    FarhanAhmed
    6 years ago
    Hire Duration = DATEDIFF('Standing Scaffold Data'[On Hire Date],

IF(ISBLANK('Standing Scaffold Data'[Off Hire Date]),TODAY(),'Standing Scaffold Data'[Off Hire Date]),DAY)

     

    Try this.

3 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago
    Hire Duration = DATEDIFF('Standing Scaffold Data'[On Hire Date],

IF(ISBLANK('Standing Scaffold Data'[Off Hire Date]),TODAY(),'Standing Scaffold Data'[Off Hire Date]),DAY)

     

    Try this.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Perhaps:

     

    Hire Duration = 
  VAR __Diff1 = DATEDIFF('Standing Scaffold Data'[On Hire Date],'Standing Scaffold Data'[Off Hire Date],DAY)
  VAR __Diff2 = DATEDIFF('Standing Scaffold Data'[On Hire Date],TODAY(),DAY)
RETURN
  IF(ISBLANK(__Diff1),__Diff2,__Diff1)
  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Anonymous ,

     

    You can try modifying your DAX as follows:

     

    Hire Duration = IF('Standing Scaffold Data'[Off Hire Date] = BLANK(), 

                                   DATEDIFF('Standing Scaffold Data'[On Hire Date], TODAY(), DAY),

                                   DATEDIFF('Standing Scaffold Data'[On Hire Date], 'Standing Scaffold Data'[Off Hire Date], DAY)

                                )

     

    Thanks,

    Pragati