cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NeilBarrett
Advocate III
Advocate III

Odd Behavior with Blank Dates

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)

 

PBI Blank Date Oddity.png

 

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @NeilBarrett,

 

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'.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Microsoft
Microsoft

Hi @NeilBarrett,

 

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'.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jthomson
Solution Sage
Solution Sage

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

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.

NeilBarrett
Advocate III
Advocate III

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)

 

PBI Blank Date Oddity.png

 

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!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.