Forum Discussion

Adam01's avatar
Adam01
Advocate I
4 years ago
Solved

Calculating Date Diff based on blank columns

Hello everyone, I have a bit of an odd one today   I am trying to create a custom column that calculates that date diff between todays date and EITHER the "Ideal Due Date" Column OR the "Fixed Dead...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi,

     

    Try the below solution

     

    Days until Deadline = 

-- Returns true if both columns are blank
var CheckBlanks = ISBLANK('Table'[Ideal Due Date]) && ISBLANK('Table'[Fixed Deadline]) 

-- Returns the difference prioritising Fixed Deadline column
var DaysDiff = int(TODAY() - COALESCE('Table'[Fixed Deadline], 'Table'[Ideal Due Date]))

return if(CheckBlanks, "No Deadlines set", CONVERT(DaysDiff, STRING))

     

     

     

PVO3's avatar
PVO3
Impactful Individual
4 years ago

Quickfix; dont use DATEDIFF. This returns all dates between given dates, not the arregrate.

Perhapse something like 

INT('Ideal Due Date'[Fixed Deadline] - 'Ideal Due Date'[Start date])
Adam01's avatar
Adam01
Advocate I
4 years ago

In this example are you using 'Ideal Due Date' as the table name for the expression 'Ideal Due Date[Fixed Deadline]'?

 

Should it be INT('Table'[Fixed Deadline] - 'Table'[Start Date]) or 'Ideal Due Date'[Fixed Deadline] intended?

 

Im returned the following if i use INT('Table'[Fixed Deadline] - 'Table'[Start Date]):

 

  • PVO3's avatar
    PVO3
    Impactful Individual
    4 years ago

    Yeah i just gave examples. I don't have a copy of your data. So you can't copy paste the exact code.

     

    But this is the direction you could go to get your problem fixed.

    If you sent a PBIX I can write the exact DAX.