# DAX DatesBetween error

• ### Question

• Hi,

I'm having trouble getting DatesBetween to work correctly.  I have a date table (DATE_DIM) with the following columns:

• DATE_DESC - Data Type = Date
• WORKING_DAY_FLAG - Data Type = Decimal Number

WORKING_DAY_FLAG is either 1 or 0 depending on whether the given date is a workday or not.

The DATE_DIM table is joined to a data table on the TRANSACTION_DATE field (data type = Date).

I want to calculate the number of elapsed working days in the current month, for example as of Jan 14, January 2016 has 8 elapsed workdays.

I've created a measure on the DATE_DIM table as follows:

MTD_ELAPSED_WORKING_DAYS:=CALCULATE(SUM([WORKING_DAY_FLAG]), DATESBETWEEN([DATE_DESC], EOMONTH(MAX(BOOKED_AND_BILLED[TRANSACTION_DATE]),-1)+1, MAX(BOOKED_AND_BILLED[TRANSACTION_DATE])))

The idea was to use DatesBetween to calculate the difference between the first day of the current month (EOMONTH(MAX(BOOKED_AND_BILLED[TRANSACTION_DATE]),-1)+1) and the most recent transaction date (MAX(BOOKED_AND_BILLED[TRANSACTION_DATE])), which should always be the previous work day in my situation.

Unfortunately, the formula gives the following error:

Semantic Error:  DatesBetween and DateInPeriod are only accepting date column reference as a first argument.

This suggests that the DATE_DESC field is not Date type, although that is what is shown for the properties of the field.

Can anyone tell me what the problem is, or maybe a different way to accomplish my goal of calculating MTD workdays?

Thanks!

Thursday, January 14, 2016 2:30 PM

• MTD Working days:
```MTD:=
TOTALMTD( SUM( Date_dim[Working_Day_Flag] ), Date_dim[Date_Desc] )```

That error is troubling. If the measure definition above doesn't work, create a calculated column in your Date_Dim witht the following definition, and let us know if it returns any false values:

```DimDateTest =
Date_dim[Date_Desc]
= DATE(
YEAR( Date_dim[Date_Desc] )
,MONTH( Date_dim[Date_Desc] )
,DAY( Date_dim[Date_Desc] )
)
)```

Check out the Power BI User Group of Philadelphia: http://PowerBIPhilly.org Our next meeting is February 2 in Malvern, PA: http://powerbiphilly.org/public/events/3802-inaugural-bimonthly-user-group-meeting

• Proposed as answer by Thursday, January 14, 2016 2:52 PM
• Marked as answer by Thursday, January 14, 2016 2:56 PM
Thursday, January 14, 2016 2:39 PM

### All replies

• MTD Working days:
```MTD:=
TOTALMTD( SUM( Date_dim[Working_Day_Flag] ), Date_dim[Date_Desc] )```

That error is troubling. If the measure definition above doesn't work, create a calculated column in your Date_Dim witht the following definition, and let us know if it returns any false values:

```DimDateTest =
Date_dim[Date_Desc]
= DATE(
YEAR( Date_dim[Date_Desc] )
,MONTH( Date_dim[Date_Desc] )
,DAY( Date_dim[Date_Desc] )
)
)```

Check out the Power BI User Group of Philadelphia: http://PowerBIPhilly.org Our next meeting is February 2 in Malvern, PA: http://powerbiphilly.org/public/events/3802-inaugural-bimonthly-user-group-meeting

• Proposed as answer by Thursday, January 14, 2016 2:52 PM
• Marked as answer by Thursday, January 14, 2016 2:56 PM
Thursday, January 14, 2016 2:39 PM
• Greg, thanks for your quick response.

I tested the date field as you suggested and no false values were returned.

Thursday, January 14, 2016 2:43 PM
• Also, the TOTALMTD function works great - thanks for the suggestion.
Thursday, January 14, 2016 2:46 PM
• I'd guess, then, that it's a poorly worded error. Does your original measure still give the same error with a fully qualified column name as its first argument: 'Date_dim'[Date_Desc] ?

Check out the Power BI User Group of Philadelphia: http://PowerBIPhilly.org Our next meeting is February 2 in Malvern, PA: http://powerbiphilly.org/public/events/3802-inaugural-bimonthly-user-group-meeting

Thursday, January 14, 2016 2:51 PM
• I'd guess, then, that it's a poorly worded error. Does your original measure still give the same error with a fully qualified column name as its first argument: 'Date_dim'[Date_Desc] ?

Check out the Power BI User Group of Philadelphia: http://PowerBIPhilly.org Our next meeting is February 2 in Malvern, PA: http://powerbiphilly.org/public/events/3802-inaugural-bimonthly-user-group-meeting

That fixed it!  Don't know why I didn't think of that.