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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeremy_R_b
Frequent Visitor

PREVIOUSMONTH produces "An invalid numeric representation of a date value was encountered" error

Hello,

 

I am fairly new to PowerBI and I'm trying to create a 'measure' that pulls a previous month

 

(using DirectQuery)  

 

This DAX measure works for pulling my current count and tests against my SQL data-check query

 

Current UniqueID Count= CALCULATE
(
DISTINCTCOUNT('Table '[UniqueID]), 'DateTable'[CreatedDateRaw])
)

The CreatedDateRaw is a 'Data type' of "Date" and I'm joining on a 'Key' that is in 'Table' (many) to 'DateTable' (one)

 

The DateTable has dates from 2000-01-01 to 2099-12-31 and one row that is 9999-12-31

 

But when I try and use PREVIOUSMONTH

 

Previous UniqueID Count= CALCULATE
      (
        DISTINCTCOUNT('Table '[UniqueID]), PREVIOUSMONTH('DateTable','DateTable'[CreatedDateRaw])
      )

I get the error ""An invalid numeric representation of a date value was encountered".

 

I have searched and it seems that maybe NULL values or possibly an invalid date may be the issue?  I know we do have a row in the date field that is 9999-12-31 as mentioned ...  So I tried to filter the measure from 2013-2020 and I still receve the error.

 

I also tried a "DATEADD, -1, Month" structure and that didn't seem to work either.

 

Full error is

 

Error Message:
MdxScript(Model) (3, 125) Calculation error in measure 'Table[MeasureName]: An invalid numeric representation of a date value was encountered.
4 REPLIES 4
v-yulgu-msft
Employee
Employee

Hi @jeremy_R_b,

 

To make PREVIOUSMONTH function supported in Directory Query mode, please

go to file -> options and settings -> options -> direct query ->"Allow unrestricted measures under direct query" .

1.PNG

 

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.

Hello Yuliani, 

 

I do have that turned on (previously turned on before working on this particular issue).

 

I'm curious if there is a better way to research what the error may be?  I'm a bit unclear on how I could get an incorrect date in the date column, I have looked through the whole table and the only entry is 9999-12-31 and -1, which I have ruled out with testing by forcing the measure to be > and < certain dates.

For what it's worth, I had the same error message when trying to use TOTALMTD on a client's model. The issue turned out to be that there was an entry in the date dimension table for 9999-12-31. When I filtered that row out of the table in the query editor, the error disappeared. This is despite the Power BI documentation saying dates to year 9999 are supported. 

jeremy_R_b
Frequent Visitor

Well, I guess it may not be supported in DirectQuery ... wonderful.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.