DATESINPERIOD
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
Syntax of the DATESINPERIOD function
The syntax of the DATESINPERIOD function is as follows:
DATESINPERIOD(❰dates❱,❰startdate❱,❰numb_intervals❱,❰interval❱)
Here are the arguments used in the syntax:
**dates:** This is a column that contains the dates that you want to filter.
**startdate:** This is the start date of the period you want to filter.
**numb_intervals:** This is the number of intervals you want to filter.
**interval:** This specifies the interval that you want to filter. The options are year, quarter, month, week, or day.
Example of using the DATESINPERIOD function
Suppose we have a table of sales data that includes a column for the sale date. We want to calculate the total sales for the last three months. We can use the DATESINPERIOD function as follows:
Total Sales Last Three Months = CALCULATE(SUM(Sales[Amount]),DATESINPERIOD(Sales[Date],LASTDATE(Sales[Date]),-3,MONTH))
Here, we are using the CALCULATE function to aggregate the sales amount. We then use the DATESINPERIOD function to filter the sales data based on the last three months. We are using the LASTDATE function to get the last date in the sales date column.
Using the DATESINPERIOD function with other functions
The DATESINPERIOD function can also be used in conjunction with other functions to provide more advanced filtering capabilities. For example, we can use the function with the FILTER function to filter data based on specific criteria.
Suppose we have a table of sales data that includes columns for the sale date, product, and sales amount. We want to calculate the total sales for the last three months for a specific product. We can use the following formula:
Total Sales Last Three Months for Product A = CALCULATE(SUM(Sales[Amount]),FILTER(Sales,Sales[Product]=”Product A”),DATESINPERIOD(Sales[Date],LASTDATE(Sales[Date]),-3,MONTH))
Here, we are using the FILTER function to filter the sales data for the product "Product A". We are then using the DATESINPERIOD function to filter the data based on the last three months.
The DATESINPERIOD function is an essential function in Power BI that enables the filtering of data based on specific time periods. By using this function, you can easily filter data based on months, quarters, years, weeks, or days. It is an excellent tool for business analysts, data analysts, and data scientists who want to analyze data based on specific time periods.
