DATESBETWEEN gives "Invalid Numeric Representation of a Date Value" Error

[mashedpotatoes]

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??

[Vvelarde]

hi @mashedpotatoes

Try this

NumDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>MIN(Shipments[DateofSale]) && Calendario[Date]<=MAX(Shipments[DateofDelivery])))

NumWorkDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>MIN(Shipments[DateofSale]) && Calendario[Date]<=MAX(Shipments[DateofDelivery])&& Calendario[Workdays]=1))

[sdjensen]

Perhaps something like this would work.

NumDays =
CALCULATE (
 COUNTROWS ( 'Calendar' ),
 FILTER (
 ALL ( 'Calendar' ),
 'Calendar'[Dates] >= MIN ( 'Shipments'[DateOfSale] )
 && 'Calendar'[Dates] <= MAX ( 'Shipments'[DateOfDelivery] )
 )
)

[mashedpotatoes]

@sdjensen Thanks for the reply! Unfortunately, that didn't quite solve my issue. Rather, it caused the column to calculated the exact same number for each row, causing some weird results in my data. I'll play around with the formula you suggested a bit more, though, as it eliminated the error I was getting!

[Vvelarde · Accepted solution]

If you want to use in a calculated column, change to this:

NumDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<= Shipments[DateofDelivery]))

NumWorkDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<=Shipments[DateofDelivery]&& Calendario[Workdays]=1))

[EvanHyer]

This worked great for me! Thanks!

[gselvag]

Thanks for this tip.

I have another challange with dates. The data is based on two tables (no relationship)

1. SCHEDULE TABLE

    Table: Tasks

    Fields: Task Name, Start Date, Finish Date, Project

2. CALENDAR TABLE

    Table: Calendar

    Fields: Date, Month, Week

My question is how can I utilize your measure formula to visualize number of days, timephased (split by e.g. month and week) by project. We need a pivot like report and a combination chart where days are summarized by month (columns).

Any tip on how to resolve this would help a lot.

Thanks

[mashedpotatoes]

@Vvelarde This worked great, thank you! And yeah, you're probably right about using a measure rather than a column. I tried to implement it as a measure initially, but couldn't quite get it to calculate the numbers I wanted. I'm not super familiar with the different usecases for calculated measures vs. columns, but I'll definitely have to look further into that. Thanks again!

[Vvelarde]

Use a measure not a calculated column