Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
Employee

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
Employee
Employee

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.