Odd Behavior with Blank Dates

[Anonymous]

Hi All,

I have a table of Support Expiry Dates for our contracts... they are listed in a column called 'Support Expiry Date'.

I've been asked to add the start date of the support periods too, these live in an assocated table, I pull these into a calculated column called 'Renewal Start Date': 

Renewal Start Date = LASTNONBLANK(LicenceChange[Renewal Start], MAX(LicenceChange[Created Date]))

And this seems to work fine - where there are blanks it's because the table doesn't have entries (for historical data entry reasons)

To deal with the unsightly blanks there I created another calculated column (which is what I ultimately want to show) called 'Support Start Date' - as a safe assumption for us is that where we don't know for certain when support started, it's likely it started a year before the expiry (most of our sales are for 1 year contracts), hence:

Support Start Date = IF(ISBLANK(Licenses[Renewal Start Date]), DATEADD(Licenses[Support Expiry Date], -1, YEAR), Licenses[Renewal Start Date])

However, for reasons I cannot fathom, this column also contains blanks! (highlighted Orange below)

So you can see the DAX works for the yellow rows ('Renewal Start Date' is blank, so it takes 1 year from the 'Support Expiry Date', but for some reason, on some rows it does not work.

I've looked through the underlying data and i can see no difference between the yellow and orange cases - so what non-obvious difference have I missed?

Initially i thought that the 'Renewal Start Date' NONLASTBLANK function might have been returning some 'different kind of blank' where the associated table was empty, so i've also tried to force the return of a Blank() where the associated table is empty via:

Renewal Start Date = IF(ISEMPTY(LicenceChange), BLANK(), LASTNONBLANK(LicenceChange[Renewal Start], MAX(LicenceChange[Created Date])))

But I get the same result.

Help appreciated!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

New a calendar table.

dim table = CALENDAR(MIN(Licenses[Support Expiry Date]),MAX(Licenses[Support Expiry Date]))

Establish a one to many relationship between 'dim table' and source table 'License'.

Modify the DAX formula for Support Start Date as below:

Support Start Date =
IF (
    ISBLANK ( Licenses[Renewal Start Date] ),
    DATEADD ( 'dim table'[Date], -1, YEAR ),
    Licenses[Renewal Start Date]
)

Best regards,

Yuliana Gu

[jthomson]

Maybe some sort of date table issue and going prior to 1990 isn't being recognised?

[Anonymous]

Happens for other more recent dates too... I don't have a date table and none of my table relationships are based on date fields.

[Anonymous]

Hi All,

I have a table of Support Expiry Dates for our contracts... they are listed in a column called 'Support Expiry Date'.

I've been asked to add the start date of the support periods too, these live in an assocated table, I pull these into a calculated column called 'Renewal Start Date': 

Renewal Start Date = LASTNONBLANK(LicenceChange[Renewal Start], MAX(LicenceChange[Created Date]))

And this seems to work fine - where there are blanks it's because the table doesn't have entries (for historical data entry reasons)

To deal with the unsightly blanks there I created another calculated column (which is what I ultimately want to show) called 'Support Start Date' - as a safe assumption for us is that where we don't know for certain when support started, it's likely it started a year before the expiry (most of our sales are for 1 year contracts), hence:

Support Start Date = IF(ISBLANK(Licenses[Renewal Start Date]), DATEADD(Licenses[Support Expiry Date], -1, YEAR), Licenses[Renewal Start Date])

However, for reasons I cannot fathom, this column also contains blanks! (highlighted Orange below)

So you can see the DAX works for the yellow rows ('Renewal Start Date' is blank, so it takes 1 year from the 'Support Expiry Date', but for some reason, on some rows it does not work.

I've looked through the underlying data and i can see no difference between the yellow and orange cases - so what non-obvious difference have I missed?

Initially i thought that the 'Renewal Start Date' NONLASTBLANK function might have been returning some 'different kind of blank' where the associated table was empty, so i've also tried to force the return of a Blank() where the associated table is empty via:

Renewal Start Date = IF(ISEMPTY(LicenceChange), BLANK(), LASTNONBLANK(LicenceChange[Renewal Start], MAX(LicenceChange[Created Date])))

But I get the same result.

Help appreciated!