Workdays Dax not working

[Anonymous]

Hello -

I have two tables - CalendarData & tblrma

CalendarData = Date table (Has a IsWorkday calculated column)

tblrma = facts table (Have total of 2 date fields that need checked)

The below DAX works in PowerPivot but does not work in PowerBI Desktop.  The error is: An invalid numeric representation of a date value was encountered.

= IF(AND([EstEmpty] = FALSE(), [POEmpty] = FALSE()),
        CALCULATE(Sum('CalendarData'[IsWorkday]),  
              DATESBETWEEN(CalendarData[Date], [estimatedate], [podate])), 
        Blank())

EstEmpty = test for blank (Checks for blank returns true false)

POEmty = Test for Blank (Checks for blank returns true false)

Any help is appreciated!

Raven

[MattAllington]

I saw this error message in another post. Is [estimatedate] always before [podate]?

[Anonymous]

No estimate date is not always before po date. 

[MattAllington]

So maybe that is the issue.  The syntax is From/To.  If from is after to, then maybe that is the issue.  You should test it.

[Anonymous · Accepted solution]

This worked after I formatted the Estimate and PO date fields to date only (no time).  goofy - but it worked right after the format.

Just in case anyone else was having problems. 

Raven

[Anonymous]

OK this error was truely because some of my date fields were messed up (ie: 5/1/151 or 5/1/203 or 5/1/2029

Mine worked when i used a query to get data - with a certain date range that did not include the messed up dates above. 

Just Learning

Raven

[GTR]

I receive this same error with my data model when trying to use any time intelligence function (previousmonth and previousyear in my case). 

For some reason, the data model automatically adds a timestamp to the end of each date type field which violates one of the requirements for a date table using time intelligence. The weird thing is, it also adds a timestamp to a MONTH_DIM table but that does not result in an error. My DATE_DIM table has no gaps or weird numbers in it after thorough insepction and analysis. 

I am using a simple formula:

=Calculate( Sum([SALES]), PREVIOUSMONTH('DATE_DIM'[FULL_DATE]) )

Any help would be appreciated. 

Thanks