cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

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?

Anonymous
Not applicable

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
Not applicable

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.