Time Intelligence Dax Functions - Power BI


Time Intelligence Dax Functions - Power BI

TOTALYTD:

Calculates the year-to-date total for a given expression.

Example: Total Sales YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date])


TOTALQTD:

Calculates the quarter-to-date total for a given expression.

Example: Total Sales QTD = TOTALQTD(SUM('Sales'[Amount]), 'Date'[Date])


TOTALMTD:

Calculates the month-to-date total for a given expression.

Example: Total Sales MTD = TOTALMTD(SUM('Sales'[Amount]), 'Date'[Date])


DATESYTD:

Returns a table that contains all dates from the beginning of the year up to the specified date.

Example: Sales YTD = CALCULATE(SUM('Sales'[Amount]), DATESYTD('Date'[Date]))


DATESQTD:

Returns a table that contains all dates from the beginning of the quarter up to the specified date.

Example: Sales QTD = CALCULATE(SUM('Sales'[Amount]), DATESQTD('Date'[Date]))


DATESMTD:

Returns a table that contains all dates from the beginning of the month up to the specified date.

Example: Sales MTD = CALCULATE(SUM('Sales'[Amount]), DATESMTD('Date'[Date]))


DATESBETWEEN:

Returns a table that contains dates that are between two specified dates.

Example: Sales Between Dates = CALCULATE(SUM('Sales'[Amount]), DATESBETWEEN('Date'[Date], DATE(2023, 1, 1), DATE(2023, 12, 31)))


SAMEPERIODLASTYEAR:

Returns a table that contains all the dates from the same period in the previous year.

Example: Sales Last Year = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))


DATESINPERIOD:

Returns a table that contains dates within a given time period.

Example: Sales Last 3 Months = CALCULATE(SUM('Sales'[Amount]), DATESINPERIOD('Date'[Date], TODAY(), -3, MONTH))


FIRSTDATE:

Returns the first date in the current context.

Example: First Sale Date = FIRSTDATE('Sales'[SaleDate])


LASTDATE:

Returns the last date in the current context.

Example: Last Sale Date = LASTDATE('Sales'[SaleDate])


PREVIOUSYEAR:

Returns a table that contains all dates from the previous year.

Example: Sales Previous Year = CALCULATE(SUM('Sales'[Amount]), PREVIOUSYEAR('Date'[Date]))


PREVIOUSMONTH:

Returns a table that contains all dates from the previous month.

Example: Sales Previous Month = CALCULATE(SUM('Sales'[Amount]), PREVIOUSMONTH('Date'[Date]))


NEXTDAY:

Returns the date that is the specified number of days after the current context date.

Example: Next Sale Day = NEXTDAY('Sales'[SaleDate])


NEXTMONTH:

Returns the date that is the specified number of months after the current context date.

Example: Next Sale Month = NEXTMONTH('Sales'[SaleDate])


NEXTQUARTER:

Returns the date that is the specified number of quarters after the current context date.

Example: Next Sale Quarter = NEXTQUARTER('Sales'[SaleDate])


NEXTYEAR:

Returns the date that is the specified number of years after the current context date.

Example: Next Sale Year = NEXTYEAR('Sales'[SaleDate])


PARALLELPERIOD:

Returns a table that contains a parallel set of dates shifted a number of intervals before or after the dates in the specified dates column.

Example: Parallel Period = CALCULATE(SUM('Sales'[Amount]), PARALLELPERIOD('Date'[Date], -1, YEAR))


LASTNONBLANK:

Returns the last value from the column that is not blank.

Example: Last Non-Blank Amount = LASTNONBLANK('Sales'[Amount], 'Sales'[SaleDate])


CLOSINGBALANCEMONTH:

Returns the closing balance for the month that contains the last value in the column, using the specified dates column to group values.

Example: Closing Balance Month = CLOSINGBALANCEMONTH('Sales'[Amount], 'Sales'[SaleDate])


CLOSINGBALANCEQUARTER:

Returns the closing balance for the quarter that contains the last value in the column, using the specified dates column to group values.

Example: Closing Balance Quarter = CLOSINGBALANCEQUARTER('Sales'[Amount], 'Sales'[SaleDate])


CLOSINGBALANCEYEAR:

Returns the closing balance for the year that contains the last value in the column, using the specified dates column to group values.

Example: Closing Balance Year = CLOSINGBALANCEYEAR('Sales'[Amount], 'Sales'[SaleDate])


These time intelligence functions are used for various calculations and analysis of time-based data in Power BI reports and dashboards.


Feel free to ask any questions about Power BI. I am here to help you.

Happy Learning! 😊







Comments

Popular posts from this blog

CONTAINSTRING DAX

Calculate Sales for Last Year Month to date

Solving the issue of "The UseRelationship() and CrossFilter() function may not be used when querying 'Table' because it is constrained by row-level security."