locked
DAX DatesBetween error RRS feed

  • 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

Answers

  • 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 greggyb Thursday, January 14, 2016 2:52 PM
    • Marked as answer by auaero 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 greggyb Thursday, January 14, 2016 2:52 PM
    • Marked as answer by auaero 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.

    Thanks for your help!

    Thursday, January 14, 2016 2:56 PM