Forum Discussion
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 Deadline" column, depending on if the column has blanks
So for example in the screenshot BELOW, for Project ID 4 I would want the date diff between Today and the "Ideal Due date" column as the "fixed deadline" column is blank for Project ID 4.
Similar to Project ID 7 I would want the date diff between Today and the "Fixed Deadline" column as the "Ideal Date Column" is blank.
The code I initially had was as follows:
Days until Deadline =
IF (
Table[Ideal Due Date].[Date] = BLANK (),
DATEDIFF ( TODAY (), Table[Fixed Deadline].[Date], DAY ),
IF (
Table[Fixed Deadline].[Date] = BLANK (),
DATEDIFF ( TODAY (), Table[Ideal Due Date].[Date], DAY ),
BLANK ()
)
)
However this code does not seem to work, has anyone got any suggestions? I am thinking I need to utlise a SWITCH function however I am not too sure
A nice to have but not necessarily needed:
Where a Project ID has both values, such as Project ID 1, I want the DateDiff function to prioritise the calculation for the "Fixed Deadline" column.
When a Project ID has neither values, return a "No Deadlines set" text string
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))
7 Replies
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])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]):
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.
And for the nice to have. I would put this in a SWITCH instead of IF because of readability.
Example:
SWITCH(TRUE(),
ISBLANK(Ideal due date) && ISBLANK(Fixed deadline), "No Deadlines set",
NOT ISBLANK(Ideal due date) && NOT ISBLANK(Fixed deadline), INT(TODAY() - Fixed deadline)
)
Implementing this code gives me the following:
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))Thank you! This is exactly what I was after!
