ENDOFYEAR
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
The syntax for the ENDOFYEAR function in Power BI is as follows:
ENDOFYEAR(❰date❱)
The ❰date❱ argument can be a reference to a column that contains dates or a formula that returns a date. The function will return the last day of the year that corresponds to the given date.
Examples
Let's take a look at some examples of how to use the ENDOFYEAR function in Power BI:
Example 1: Calculating Sales at the End of the Year
Suppose you have a sales table that contains the date of each sale and the corresponding amount. You want to calculate the total sales for each year and visualize the results in a chart. You can use the ENDOFYEAR function to group the sales by year and calculate the total amount for each year.
Sales by Year =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Date] ❰= ENDOFYEAR(MAX(Sales[Date]))
),
VALUES(Sales[Year])
)
In this example, we first filter the sales table to include only the sales that occurred on or before the last day of the current year. We then group the sales by year and calculate the sum of the amount for each year. The VALUES function is used to ensure that the year is displayed in the chart even if there are no sales for that year.
Example 2: Calculating Year-to-Date Sales
Suppose you want to calculate the year-to-date sales for each product category based on the current date. You can use the ENDOFYEAR function to determine the end of the current year and then filter the sales table to include only the sales that occurred on or before the current date.
Year-to-Date Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Date] ❰= TODAY() &&
Sales[Date] ❱= DATE(YEAR(TODAY()),1,1) &&
Sales[Date] ❰= ENDOFYEAR(TODAY())
),
Sales[Category]
)
In this example, we first use the TODAY function to determine the current date. We then use the DATE function to determine the first day of the current year. We use the ENDOFYEAR function to determine the end of the current year. Finally, we filter the sales table to include only the sales that occurred on or before the current date and belong to the specified product category.
The Power BI DAX function ENDOFYEAR is a powerful tool that can help you analyze and visualize data in a meaningful way. This function allows you to calculate the last day of the year based on a given date, which can be useful for a variety of applications. We have explored how to use the ENDOFYEAR function in Power BI and provided examples of its applications. By mastering this function, you can take your data analysis and visualization skills to the next level.
