Measure not working due invalid numeric representation of Date value

[damit23183]

Hi,

I am trying to create simple Measur by average of counting ID based on some conditions depending on dates columns from two different tables.

Here is formula i am trying to replicate based on existing dashboard where this formula is working completely fine.

Total Population = AVERAGEX('Date', CALCULATE(DISTINCTCOUNT('Current'[id]),
FILTER('Current','Current'[StartTime] <= LASTDATE('Date'[Date]) &&
(ISBLANK('Current'[EndTime]) || 'Current'[EndTime] >= FIRSTDATE('Date'[Date])))))

Here Start Date is fine but as soon as i add End Time its creating an issue;

END DATE is default which (Tried to change format and applied following value for End Date :

CONVERT(datetime, O.[EndTime] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') as [EndTime]

'9999-12-31 18:00:00.000' as [EndTime]

'9999-12-31 6:00:00.000 PM' as [EndTime]

But it is giving me this error message;

Also there is no relationship set up between Date and Current table because Date is calander table created directly in PowerBI and all other tables are coming from Azure SQL Database. Further, same logic is working for other measures but as soon as i added CURRENT table with END TIME it crashed and because of that whole existing dashboard generated same errors on all visulization.

What i found is, END DATE is the issue because when i remove end date from query everything(All Visulizations, Measures, Tables etc) is working fine but i need END DATE from current table to count other ID because i am comparing number of different IDs from different table and current table is one of them.

Any help appreciated

Thanks

[Anonymous]

Hi  @damit23183 ,

Based on my test, this can be caused by an invalid datetime value that exceeds the year 9999.

See the similar thread below:

https://community.powerbi.com/t5/Desktop/Error-with-SAMEPERIODLASTYEAR-an-invalid-numeric-representa...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @damit23183，

Is the data type of EndTime same with the data type of 'Date'[Date] and StartTime?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[damit23183]

Yes it is same data type.

Thanks

[parry2k]

@damit23183 what is enddate data type ? Is it date/time or date/timezone?

[damit23183]

Hi,

It is Date Time becuase End date is comparing with Date from Date table which has same data type.

Thanks