Problem with my DATEDIFF formula

[RvdHeijden]

Ive got a formula that calculates the difference between 2 dates (Ticketstart and Ticketstop)

If 'Ticketstop' is empty it uses TODAY() and calculates the difference.

However my formula now returns this this error:

An invalid numeric representation of a date value was encountered.

I can't seem to find them problem because the datatypes of both colums is set to 'Date/Time' with the same Format.

This is the formula, can anyone help me ?

Doorlooptijd Ticket = VAR DIFF =
    CALCULATE (
        COUNTROWS ( 'Date' );
        ALL ( 'Date' );
        DATESBETWEEN (
            'Date'[Date];
            Tickets[Ticketstart];
            IF ( ISBLANK ( Tickets[Ticketstop]); TODAY (); Tickets[Ticketstop] )
        );
        'Date'[Weekday] = TRUE()
    )
RETURN
   IF ( DIFF= 1; 1; DIFF)

[v-yulgu-msft · Accepted solution]

Hi @RvdHeijden,

I could reproduce above error with below scenario:

• The date value of Date[Date] starts from '2015-01-01' to '2017-12-31'
• Tickets[Ticketstart]='2013-12-31' and Tickets[Ticketstop]='2016-01-01'
• The error was prompted when I used DateBetwee(Date[Date],Tickets[Ticketstart],Tickets[Ticketstop])

If Tickets[Ticketstart]='2014-1-1', everything will be OK.

From above test, we can know that in DATESBETWEEN(<dates>,<start_date>,<end_date>), the year value of <start_date> cannot be smaller than the last year of minimum of <dates>. Otherwise, error will be thrown.

To resolve this problem, in your scenario, if Date[Date] comes from a calendar table, you just need to enlarge the date range of calendar table, especially the start date.

If you cannot change the value of Date[Date], please modify your DAX formula as below, using Filter instead of DatesBetween.

Doorlooptijd Ticket2 = VAR DIFF =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        ALL ( 'Date' ),
        FILTER( 
            'Date',
            'Date'[Date]>=Tickets[Ticketstart] &&
            'Date'[Date]<= IF ( ISBLANK ( Tickets[Ticketstop]), TODAY (), Tickets[Ticketstop] )
        ),
        'Date'[Weekday] = TRUE()
    )
	return
   IF ( DIFF= 1, 1, DIFF)

 Best regards,
Yuliana Gu

[RvdHeijden]

@v-yulgu-msft

I had to calculate a date from a UNIX number using this formula:  

Aanmaakdatum = DATEVALUE("1/1/1970")+(Tickets[datum]/86400)

and sometimes there wasn't any so it read 01-01-1970 and my date table wasn't going back so far. I changed it and now it works, thanks for the help