Incorrect DAX

[Anonymous]

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?

[Pragati11]

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

[Greg_Deckler]

@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)

[FarhanAhmed · Accepted solution]

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.