How to Use DATEDIFF to Calculate Date # and Fiscal Date # in a Year

Posted on by Summa Lai

DATEDIFF returns the count of interval boundaries crossed between two dates.

Syntax

DAXCopy

DATEDIFF(<start_date>, <end_date>, <interval>)

Parameters

TermDefinition
start_dateA scalar datetime value.
end_dateA scalar datetime value Return value.
intervalThe interval to use when comparing dates. The value can be one of the following:

– SECOND
– MINUTE
– HOUR
– DAY
– WEEK
– MONTH
– QUARTER
– YEAR

Below, see a sample to calculate Date # and Fiscal Date # in a Year.

DateNo = DATEDIFF(DATE(YEAR(‘Calendar'[Date]),1,1),’Calendar'[Date],DAY)+1

Below, are the sample for calculate the Fiscal Date Number (Fiscal Year starting on Feb 1)

Fiscal_DateNo =
IF(‘Calendar'[DateNo]>31,’Calendar'[DateNo]-31,
DATEDIFF(DATE(YEAR(‘Calendar'[Date])-1,2,1),Date(YEAR(‘Calendar'[Date])-1,12,31),DAY)+DATEDIFF(DATE(YEAR(‘Calendar'[Date]),1,1),’Calendar'[Date],DAY)+2)