CALENDAR
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 CALENDAR function?
The CALENDAR function is a DAX function that creates a table of dates. It takes two arguments, a start date and an end date, and returns a table with one column containing all the dates between those two dates. The syntax for the CALENDAR function is as follows:
CALENDAR(Start_Date, End_Date)
The Start_Date and End_Date arguments are required and must be in date format.
Creating a Date Table using the CALENDAR function
A date table is a table that contains all the dates in a given range. It is useful for analyzing data by date, such as sales by day or month. Here’s how to create a date table using the CALENDAR function:
1. Open Power BI and go to the “Modeling” tab.
2. Click on “New Table” and enter the following formula:
Dates = CALENDAR(DATE(Year, Month, Day), DATE(Year, Month, Day))
Replace “Year”, “Month”, and “Day” with the corresponding values for your start and end dates. For example, if you want to create a date table for the year 2021, the formula would be:
Dates = CALENDAR(DATE(2021, 1, 1), DATE(2021, 12, 31))
3. Click “Enter” and the date table will be created.
Customizing the Date Table
After creating the date table, you can customize it to fit your needs. Here are some common customizations:
Adding Columns
You can add columns to the date table to analyze data by specific time frames. For example, you can add a column for the year, quarter, month, or day of the week. To add a column, use the following formula:
Column Name = FORMAT(Date Column, "Format")
Replace “Column Name” with the name of the new column, “Date Column” with the name of the existing date column in your table, and “Format” with the desired format for your new column. For example, to add a column for the year, use the following formula:
Year = FORMAT(Dates[Date], "YYYY")
Filtering Dates
You can filter the date table to show data for specific time frames. For example, you can filter the table to show data for a specific year or month. To filter the table, use the following formula:
Filtered Table = FILTER(Date Table, Condition)
Replace “Filtered Table” with the name of your filtered table, “Date Table” with the name of your original date table, and “Condition” with the desired condition for your filter. For example, to filter the table to show data for the year 2021, use the following formula:
Filtered Dates = FILTER(Dates, Dates[Year] = "2021")
Sorting Dates
You can sort the date table by any column to make it easier to analyze data. To sort the table, use the following formula:
Sorted Table = SORT(Date Table, Column, DESC/ASC)
Replace “Sorted Table” with the name of your sorted table, “Date Table” with the name of your original date table, “Column” with the column you want to sort by, and “DESC/ASC” with “DESC” to sort the column in descending order or “ASC” to sort the column in ascending order. For example, to sort the table by month in descending order, use the following formula:
Sorted Dates = SORT(Dates, Dates[Month], DESC)
The CALENDAR function is a useful tool for creating a date table in Power BI. With this function, you can create a table of dates, customize it to fit your needs, and analyze your data by specific time frames. By mastering the CALENDAR function, you can take your Power BI skills to the next level.
