Calculated column (date) not returning expected results

[LC_RepAC]

Hi, I have a table pulling [PrimaryKey], [Application], [Start Date] and [End Date] values from an existing table.

The rest of the new table is mostly made of calculated columns.

The calculated columns that are causing me grief are as follow:

CheckDate1 = IF(MultipleEndDateTable[Combo]="1Y", BLANK(),DATEADD(MultipleEndDateTable[Start Date].[Date],1,YEAR))

CheckDate2 = IF(MultipleEndDateTable[Combo]="2Y", BLANK(),DATEADD(MultipleEndDateTable[CheckDate1].[Date],1,YEAR))

CheckDate3 = IF(MultipleEndDateTable[Combo]="3Y", BLANK(),DATEADD(MultipleEndDateTable[CheckDate2].[Date],1,YEAR))

CheckDate4 = IF(MultipleEndDateTable[Combo]="4Y", BLANK(),DATEADD(MultipleEndDateTable[CheckDate3].[Date],1,YEAR))

etc...

The point is to only return a new Checkpoint Date if:

1) the Combo counter differs from a specific value

AND

2) the previous calculated date in the series is not blank

As you can see from the screenshot:

CheckDate1 returns the correct values (even on a bigger data sample)

CheckDate2 doesn't always return the expected values (see cell in red, which should contain a value)

Ditto for CheckDate3, CheckDate4, etc...

Cells in red => missing value

Cells in green => correct

I edited the formulae to something much simpler, e.g.

CheckDate2 = IF(MultipleEndDateTable[Combo]="1Y", BLANK(), "Checkpoint Date")

This returned accurate results, so it's either something to do with the DATEADD function or the fact that calculations can't be based on other calculations because you can't predict which calculations will take place first.

I guess I should be using something else to get to the desired output. That, or I'm failing to spot an obvious mistake in my formula...

Thanks in advance to whoever can help me with this 🙂

LC

[Anonymous]

@LC_RepAC 

Please read the documentation of the function you're trying to use: https://dax.guide/DATEADD.

[amitchandak]

@LC_RepAC , Dateadd need the continuous date , in place of that use date like

example

CheckDate1 =
var _1 = MultipleEndDateTable[Start Date].[Date]
return
IF(MultipleEndDateTable[Combo]="1Y", BLANK(),date(year(_1) +1, month(_1), day(_1)))

refer:

https://www.youtube.com/watch?v=YWo-ZpKM6gU

https://www.youtube.com/watch?v=9qiRivlBv8w