Date Diff. Using Today() function

[vishalbaldania]

As you can see in below image I have four calculate column:

Over Due - Days: Excel Formula: =IF(ISBLANK(D2),"",IF(D2<TODAY(),DATEDIF(D2,TODAY(),"d"),"Days Next Due"))

Next Due - Days: Excel Formula: =IF(ISBLANK(D2),"",IF(D2>=TODAY(),DATEDIF(TODAY(),D2,"d"),"Days Over Due"))

Over Due - Meter 1 : Excel Formula: =IF(ISBLANK(E2),"",IF(E2<B2,B2-E2,"KMs Next Due"))

Next Due - Meter 1: Excel Formula: =IF(ISBLANK(E2),"",IF(E2>=B2,E2-B2,"KMs Over Due"))

Is it possible in Power BI Desktop? If yes, can you please tell me steps of formula.

Please find attached images

[vanessafvg]

@vishalbaldania  its basiscally the same, except replace the cell value with the column name and datedif is datediff with 2 ff,

[vishalbaldania]

I created column and i was trying to write same things but it's says Datediff is not define function - See below photo

I also Created Measure and i was not able to select my column after use If - See below Photo

[vanessafvg]

@vishalbaldania are your dates of a  date data type?  (dont think thats the problem though)

its definitely a function i use it all the time, are you donig this in power bi? did you copy and past yoru column in or did you grab it from intellisense?

[vishalbaldania]

@vanessafvgPlease see in image - I am doing in power bi and i am pulling my data from oracle data base

[vanessafvg]

@vishalbaldania are you in imported mode or live connection?

[vishalbaldania]

@vanessafvg Imported mode

[vanessafvg]

@vishalbaldania ive tried the formula and for me it works fine in a column not a measure, like i said is your date of date data type in power bi?  i.e not a string?

[vishalbaldania]

@vanessafvg Can you please take a screen short of your formula, so i can see why it's not working.

[vanessafvg]

@vishalbaldania 

[vanessafvg]

@vishalbaldania i must say that is rather odd

things i would check

1) is your date a date type? (even if it was date at the source you might need to set it)

2) what version of power bi are you on

3) can you use dateadd()?

4) are you creating this column in the correct table? ie. the table where your date is

5) is it definitely a column?

[vishalbaldania]

@vanessafvg I have also tried but it's not working,

When i  use this column my main table show up : Can't display

[vishalbaldania]

@vanessafvg When i upload excel sheet in to power bi, the formula is working

but when i am working with my actual data set which is avalible in Orecal it's not working... Do you what should i do?

[vanessafvg]

 @vishalbaldania and you importing it from oracle? or is it a live connection?  there is limited functionality with live connections

[vishalbaldania]

@vanessafvg Sorry, I missed it. It was in live that why, i was getting problem. but after changed in to imported it's working.

But As you can see in below image thare are some cells are empty i want to write there "Days Next Due". Like in excel formula:

Over Due - Days: Excel Formula: =IF(ISBLANK(D2),"",IF(D2<TODAY(),DATEDIF(D2,TODAY(),"d"),"Days Next Due"))

Power BI Formula: Column = IF(EQ_SCHEDJOB[NEXT_DUE_DT] < TODAY(), DATEDIFF('EQ_SCHEDJOB'[NEXT_DUE_DT], TODAY(), DAY)) - Working Good

Column = IF(EQ_SCHEDJOB[NEXT_DUE_DT] < TODAY(), DATEDIFF('EQ_SCHEDJOB'[NEXT_DUE_DT], TODAY(), DAY, "Days Next Due")) - Getting Error please see image

[vanessafvg]

@vishalbaldania

so I understand what you asking, what is the objective of your formula?

to check if there is date, if there is and its < than today, run the date diff and return a day value, otherwise, if not then put the text in the same column 'days next due'? or are you trying to name the column?

[vishalbaldania]

@vanessafvg The objective of the formula is:

Excel Formula: IF(D2<TODAY(),DATEDIF(D2,TODAY(),"d"),"Days Next Due"))

The D2 cell has next due date for the equipment and if it < today's date then result would be the text "Days Next Due" intend of empty cell.

and if the next due date for the equipment > today's date then result would be the "Number in days like: 127, 70..."

As you can see in power bi image there are so many empty cell are there but in excel sheet it's occupied. If it is possible, my report would be very easy to understand for the user.

Please find attached photo

[vanessafvg]

@vishalbaldania its because you are mixing your data types, you are getting a numeric calculation but you want to then put a text in the column.  This is where its different to excel because excel wont mind but power bi will.  You will need to come up with a numeric indicator for days next due like -1 or something.