A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the DATEADD Function in DAX?
The DATEADD function in DAX is a time intelligence function that can be used to add or subtract a specified number of units to a date. The syntax of the function is as follows:
DATEADD(❰column❱,❰number❱,❰unit❱)
- The `❰column❱` parameter specifies the column that contains the date value.
- The `❰number❱` parameter is the number of units to add or subtract from the date value.
- The `❰unit❱` parameter is the type of unit to add or subtract, such as "day", "month", "quarter", "year", etc.
For example, if you want to add 30 days to a date value in a column called "Order Date", you can use the following formula:
DATEADD(‘Sales'[Order Date], 30, DAY)
This will return a new date value that is 30 days later than the original date value in the "Order Date" column.
Using the DATEADD Function in Power BI
Now that we understand the syntax of the DATEADD function, let's explore some examples of how to use it in Power BI.
Example 1: Adding Days to a Date
Suppose you have a dataset that contains a column of order dates and you want to add 7 days to each date. Here's how you can do it:
1. Create a new measure by clicking on "New Measure" in the "Modeling" tab.
2. Enter the following formula:
New Date = DATEADD(‘Sales'[Order Date], 7, DAY)
3. Click "OK" to create the measure.
4. Add the measure to a visual to see the updated date values.
Example 2: Subtracting Months from a Date
Suppose you have a dataset that contains a column of birth dates and you want to subtract 12 months from each date. Here's how you can do it:
1. Create a new measure by clicking on "New Measure" in the "Modeling" tab.
2. Enter the following formula:
New Date = DATEADD(‘Customers'[Birth Date], -12, MONTH)
3. Click "OK" to create the measure.
4. Add the measure to a visual to see the updated date values.
Example 3: Adding Years to a Date
Suppose you have a dataset that contains a column of hire dates and you want to add 3 years to each date. Here's how you can do it:
1. Create a new measure by clicking on "New Measure" in the "Modeling" tab.
2. Enter the following formula:
New Date = DATEADD(‘Employees'[Hire Date], 3, YEAR)
3. Click "OK" to create the measure.
4. Add the measure to a visual to see the updated date values.
The DATEADD function in DAX is a powerful tool that can help you to manipulate date values in Power BI. By adding or subtracting a specified number of units to a date, you can perform a variety of time-based calculations and gain deeper insights into your business data. We hope this article has helped you to understand how to use the DATEADD function in Power BI.