Forum Discussion

Adam01's avatar
Adam01
Icon for Advocate I rankAdvocate 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
Icon for Impactful Individual rankImpactful 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
    Icon for Advocate I rankAdvocate 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
      Icon for Impactful Individual rankImpactful 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.