CODEWITHSUNDEEP
datepowerpivotpowerbidax
mashedpotatoes
mashedpotatoes

Reputation: 33

Power BI: DAX formula to find number of days between dates gives "an invalid numeric representation of a date value was encounted" error

I've just recently begun using Power BI and DAX for my new job, so if this is a super simple or stupid fix I apologize.

I'm trying to create a column (NumDays) that calculates the number of days within a date range, and a column (NumWorkDays) that calculates the number of work days between the date range. I have a table (Calendar) that contains a list of dates and indicates with 1 or 0 whether it's a work day or not (Mon-Fri). In a separate table (Shipments), I have two date values I'm trying to find the number of work days between: DateOfDelivery and DateOfSale.

Here are my DAX formulas for both columns...

NumDays:

NumDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(COUNTROWS('Calendar'),DATESBETWEEN('Calendar'[Dates],'Shipments'[DateOfSale], 'Shipments'[DateOfDelivery])))

NumWorkDays:

NumWorkDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(SUM('Calendar'[IsWorkDay]),DATESBETWEEN('Calendar'[Dates],'Shipments'[DateOfSale], 'Shipments'[DateOfDelivery])))

Whenever I try to run either formula, I get this error:

An invalid numeric representation of a date value was encountered.

And that's it. I've gone through both tables looking for weird values, and I've double checked the data types to make sure they are datetimes and not something funky. Honestly I'm pretty stumped here, so any help would be appreciated. Thanks in advance!

**Edit: I've tried breaking down the parts of each formula to try and pinpoint where the error is being caused. It seems like it's originating from the DATESBETWEEN() function, but I can't narrow it down to a particular column or table causing the issue. Is this just a known issue with that function??

Upvotes: 0

Views: 4637

Answers (2)

Francesco
Francesco

Reputation: 372

Please check dates in the tables.

In my experience I have received the same error when in a table I put rows and in a line there was a wrong date with year equals to 9999. The strange things is that date column hasn't been used in any DAX formula.

Upvotes: 0

mashedpotatoes
mashedpotatoes

Reputation: 33

Got an answer from someone on the Power BI Community forums... Turns out using DATESBETWEEN() wasn't the right way to go.

Updated NumDays:

NumDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(COUNTROWS('Calendar'),filter('Calendar', 'Calendar'[Dates]>('Shipments'[DateofSale]) && 'Calendar'[Dates]<=(''[Delivered]))))

Updated NumWorkDays:

NumWorkDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(COUNTROWS('Calendar'), filter('Calendar', 'Calendar'[Dates]>('Shipments'[DateofSale]) && 'Calendar'[Dates]<=('Shipments'[DateOfDelivery])&& 'Calendar'[WorkDay]=1)))

Here's the link to the post on their site: http://community.powerbi.com/t5/Desktop/DATESBETWEEN-gives-quot-Invalid-Numeric-Representation-of-a-Date/m-p/42604#M16306

Upvotes: 0

Related Questions