TOTALYTD Issue with 99991231 date in Date Dimension

[Anonymous]

Has anyone else experienced an issue when trying to use TOTALYTD function when they have the date 9999-12-31 in their Date dimension?

We have a Fact table where workers are set targets on a monthly basis, i wanted to create a YTD measure for this (pretty simple so i thought!!)  However i kept getting the following error.

Error Message:
MdxScript(Model) (4, 46) Calculation error in measure 'Fact WorkerTargets'[Target Fees YTD]: An invalid numeric representation of a date value was encountered.

The fact table has a TargetDateKey field (date key is an integer in the format of  YYYYMMDD) linked to the date dimension table  via simialr key on Date Dimension Table ([DateKey]), the date dimension has a [Date] field which has the date stored in a proper Date format and it is set as a Date datatype in the model.

My Targets YTD  Measure on my Fact Table looks like this

Target Fees YTD = TOTALYTD(SUM('Fact WorkerTargets'[Target Fees]),'TargetDate'[Date],ALL('TargetDate'),"30 Apr")

However if i dont exclude the date 99991231 from the datetable then i get the above error and the graphic doesn't display

Exclude the date and everything works ok.

Any ideas?

Thanks

[mark_i_roberts]

had similar issue.  Rather than removing the date Dec 31st 9999 which is valid and often used date from the date table.

Try:
create a new column in the date table called say 'DateExcl9999' which is defined as
= if( [Date Key] < 90000101,[Date])

[Date Key] here is an integer yyyymmdd so it is happy to consider values such as Jan 1st 9000 or 999991231 as it doesn't have to consider these valid dates.

so [DateExcl9999] will be blank/null for all dates in Year 9999.  Redefine your Target Fees YTD  function using this new date.  So something like:

Target Fees YTD := TOTALYTD(
    SUM('Fact WorkerTargets'[Target Fees]),'
     TargetDate'[DateExcl9999],
     ALL('TargetDate'),
     "30 Apr")

[v-yuezhe-msft]

@Anonymous,

I am afraid that you would need to exclude the 9999/12/31 date from the date table.

Based on my test, when we don’t exclude 9999/12/31 from date dimension table, same error will occur even we use DATSYTD function to calculate the measure.

Target Fees YTD = CALCULATE (SUM('Fact WorkerTargets'[Target Fees]), DATEDYTD('TargetDate'[Date],"30 Apr"),FILTER('TargetDate', 'TargetDate'[DateKey]<99991231))



Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

so is this a Power BI issue , or a DAX engine issue?

9999-12-31 is a common unknown end date sceanrio in DWHing, how are other people handling this? seems daft you have to exclude it?!?

thanks

[v-yuezhe-msft]

@Anonymous,

Based on my test, we have to exclude the date from Date table. 

Regards,
Lydia

[Anonymous]

I've also tried rewriting the query filtering out the date  rather than removing it from the table explicitly however i now get the error