DATE AND TIME FUNCTIONS

These are functions for manipulating dates or time periods given in DateTime format. Following are some of the most used Date and Time functions of DAX, along with their syntax and examples:

DAX CALEND

The CALENDAR function returns a table with a single column of dates. The start_date and end_date arguments can be any DateTime value.

SYNTAX –>

CALENDAR(<StartDate>, <EndDate>)

E.G –>

COUNTROWS(CALENDAR(DATE(2017, 7, 3), DATE(2022, 4, 2))) //  it returns 1735

DAX DATEDIFF

The DATEDIFF function calculates the difference between two dates and returns the result based on the specified interval (e.g., hours, days, months).

SYNTAX –>

DATEDIFF(<StartDate>, <EndDate>, <Interval>)

E.G –>

DATEDIFF(DATE(2024, 2, 2), DATE(2024, 3, 31), HOUR)   //  it returns 71344

DATEDIFF(DATE(2024, 3, 3), DATE(2024, 4, 11), DAY)    //  it returns 39

DATEDIFF(DATE(2024, 4, 4), DATE(2024, 4, 30), MONTH)  // it  returns 0

DAX NOW

The NOW function returns the current DateTime value in the standard format. SYNTAX –>

SYNTAX –>

NOW()

E.G –>

NOW() // it returns the current DateTime

DAX DATEVALUE

The DATEVALUE function lets Excel convert a date in text format into a DateTime value

SYNTAX –>

DATEVALUE(<DateText>)

E.G –>

DATEVALUE(“19/11/2023”)         //  it returns 2023-11-19 00:00:00

DATEVALUE(“20-6-2024”)        //  returns 2024-06-20 00:00:00

DATEVALUE(“01-Jan-2023”)      //  it returns returns 2023-01-01 00:00:00

TIME INTELLIGENCE FUNCTION

Time intelligence functions allow the calculation of time values for periods like weeks, months, quarters, and years. These functions mostly deal with aggregation, data manipulation, and Business Intelligence; therefore, a table with dates is usually expected.

DAX DATEADD

The DATEADD function returns a table containing the shifted dates forward or backward based on a specified number of intervals.

SYNTAX –>

DATEADD(<Dates>, <Number_of_Intervals>, <Interval>)

E.G –>

DATEADD(ProductInventory[InventoryDate], 1, YEAR) //This will shift all dates in the InventoryDate column forward one year.

DAX DATSBETWEEN

The DATESBETWEEN function returns a table comprising a column of dates between the start date and end date specified.

SYNTAX –>

DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)

E.G –>

CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Sales[Date], DATE(2024, 6, 5), DATE(2024, 7, 3))) //This will return the total SalesAmount for a given date range.

DAX LASTDATE

The LASTDATE function returns the last date in the specified column of dates.

SYNTAX –>

LASTDATE(<Dates>)

E.G –>

LASTDATE(Sales[Date]) // It returns last date in the Sales date column

DAX ENDOFYEAR

The ENDOFYEAR function returns the last date of the year in the current context for the specified dates column. Optionally, a different year-end date can be provided. **Syntax:**

SYNTAX –>

ENDOFYEAR(<Dates>, [<Year_End_Date>])

E.G –>

ENDOFYEAR(Start[Date]) //This will return the last date of the year for the Start date column.

LOGICAL FUNCTIONS

DAX Logical functions are used to carry out a logical operation and return a result in TRUE or FALSE form. A few of the DAX logical functions are given below.

DAX AND FUNCTION

The AND function checks whether both arguments are TRUE or FALSE. It will return TRUE only if all arguments are true; otherwise, it is FALSE.

SYNTAX –>

AND(<argument1>, <argument2>)

E.G –>

AND([Country] = “PORTUGAL”, [Medal] = “SILVER”) // Returns TRUE if both conditions are TRUE.

DAX OR FUNCTION

This function will return True if at least one or both of the arguments are True, otherwise it will return False.

SYNTAX –>

OR(<argument1>, <argument2>)

E.G –>

OR([Medal Count] < 50, [Count of Sports] >50) //Returns TRUE if any of the conditions is true

DAX IF FUNCTION

It checks the first argument given in the statement. If True is the condition, the function returns the first value; otherwise, it returns the second.

SYNTAX –>

IF(<condition>, <true_value>, <false_value>)

E.G –>

IF([Country] = “FRANCE”, 1, 0) //Returns 1 if the condition is TRUE; otherwise returns 0.

DAX SWITCH FUNCTION

This function takes arguments and returns one of the listed values against it.

SYNTAX –>

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, …, [<Else>])

E.G –>

SWITCH([MONTHS], 1, “JANUARY”, 

    2, “FEBRUARY”, 

    3, “MARCH”, 

    4, “APRIL”, 

    5, “MAY”, 

    6, “JUNE”, 

    7, “JULY”,

    8, “AUGUST”,

    9, “SEPTEMBER”,

    10, “OCTOBER”,

    11, “NOVEMBER”,

    12, “DECEMBER”,