Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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,...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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