Table Vs Query

[ckennedy]

I'm sure you will be able to tell from my questions that I can a complete newbie. Thanks in advance for your patience.

I'm working with an excel spreadsheet and I have a column called Next Assessment Date which includes some dates and some blanks. Some dates are in the past and some are in the future. When I look at the table in PBI, the Next Assessment Date column in blank. When I look at the query, the same column has dates values and "null" appropriately.

I would like to add a new column. The values in the new column would be calculated based on a formula like this: 

Column = if(isblank([Next Assessment Date]),0,datediff([Next Assessment Date],today(),day)).. which produces an error: start date cannot be greater than the end date.

My questions are:

Why do I only see date values and "null" in Next Assessment Date column in the query (as opposed to the table)?

Should I add the column to the table or the query? Why?

[v-yulgu-msft]

Hi @ckennedy,

As you said, "Some dates are in the past and some are in the future.", and the start date cannot be greater than the end date when you use DATDIFF function. 

So, please try this:

Column =
IF (
    ISBLANK ( [Next Assessment Date] ),
    0,
    IF (
        Table3[Next Assessment Date] < TODAY (),
        DATEDIFF ( [Next Assessment Date], TODAY (), DAY ),
        DATEDIFF ( TODAY (), [Next Assessment Date], DAY )
    )
)

Best regards,

Yuliana Gu

[ckennedy]

Thank you Yuliana. This got rid of the error in my new column I'm calling [Days Since NAD].

Now, the [Next Assessment Date] column is completely blank. I see date values in the query, but not the table. What would cause an entire column to be blank? I'm assuming this is why [Days Since NAD] (using the new formula) has all zeros.

[v-yulgu-msft]

Hi @ckennedy,

What do you mean "Query" and "Table"? You said the [Next Assessment Date] column is completely blank, was this column an original field in source data or a calculated column? Can you post a screenshot to show the result you got?

Regards,

Yuliana Gu

[ckennedy]

Sorry, I wasn't very clear.

When I go to the table, [Next Assessment Date]  has no values in the column. When I edit the query, the [Next Assessment Date] column has "null" or date values as I would expect.

I'm wondering why values are missing in the table.

TABLE

QUERY

[v-yulgu-msft]

Hi @ckennedy,

Do you mean there isn't any data in [Next Assessment Date]  in Table view? 

The row order is not the same in TABLE and QUERY. If there is a large number of data records in table, please sroll down o check whether all rows are empty. Please check whether below highlighted records exists in TABLE.

Regards,

Yuliana Gu

[ckennedy · Accepted solution]

I figured it out. Thanks for the help!

[Anonymous]

@ckennedy,

The syntax for DATEDIFF is DATEDIFF(<start_date>, <end_date>, <interval>). Since you have some dates in the past and future, the error is produced. 

For Next Assessment, can you provide your sample PBIX file?