Introduction

Ever wondered how some dashboards instantly compare today’s sales with last month’s totals? The secret lies in Power BI Time Functions, the unsung heroes of DAX that unlock dynamic date calculations with a single measure. In this article, we’ll explore ten essential Power BI Time Functions, showing you exactly how and why to use each one for actionable, time-based insights. Whether you need year-to-date figures or period-over-period growth, mastering these functions will transform your reports—and accelerate your decision-making.

1. Power BI Time Functions – TODAY

Description: Returns the current date, updating at each data refresh.
Syntax:

scssCopyTODAY()

Use Case:
For example, you might build a measure like:

iniCopyDaysSinceLastSale = DATEDIFF(LASTDATE(Sales[Date]), TODAY(), DAY)  

to show how many days have passed since the last recorded sale. Moreover, you can filter visuals to show “Transactions in the last X days,” always relative to the report’s refresh date.

“Time is what we want most, but what we use worst.” – William Penn

---

2. Power BI Time Functions – NOW

Description: Returns the current date and time, perfect for timestamping.
Syntax:

scssCopyNOW()

Use Case:
Imagine adding a “Last Refresh” card on your dashboard. By creating a measure:

iniCopyLastRefresh = NOW()  

and formatting it for date and time, you provide users with immediate context about data currency—an easy credibility booster. Consequently, whenever stakeholders see stale numbers, they know exactly when the data was last updated.

---

3. Power BI Time Functions – DATEDIFF

Description: Calculates the difference between two dates in specified units (DAY, MONTH, YEAR, etc.).
Syntax:

php-templateCopyDATEDIFF(<StartDate>, <EndDate>, <Interval>)

Use Case:
To compute the average customer age in days:

iniCopyCustomerAge = DATEDIFF(Customer[BirthDate], TODAY(), DAY)  

Alternatively, you could calculate month-over-month sales growth:

sqlCopyMOM_Growth = 
DIVIDE(
  [Sales This Month] - [Sales Last Month],
  [Sales Last Month]
)

where [Sales Last Month] uses DATEDIFF internally. This function’s versatility makes it indispensable for interval analysis.

---

4. Power BI Time Functions – DATEADD

Description: Shifts a date by a specified number of intervals.
Syntax:

php-templateCopyDATEADD(<Dates>, <NumberOfIntervals>, <Interval>)

Use Case:
To build a “Sales Last Year” measure:

sqlCopySales_LY = CALCULATE(
  [Total Sales],
  DATEADD(Calendar[Date], -1, YEAR)
)

This moves the context back exactly one year. Therefore, combining DATEADD with CALCULATE helps you effortlessly compare performance across equivalent periods.

---

5. Power BI Time Functions – PARALLELPERIOD

Description: Shifts dates in parallel periods (e.g., same month last year).
Syntax:

php-templateCopyPARALLELPERIOD(<Dates>, <NumberOfPeriods>, <Interval>)

Use Case:
Use this to compare the same quarter last year:

sqlCopyQtrSales_PY = CALCULATE(
  [Quarter Sales],
  PARALLELPERIOD(Calendar[Date], -1, YEAR)
)

Unlike DATEADD, PARALLELPERIOD maintains consistent granularity. Thus, it’s ideal when comparing parallel chunks, such as quarters or months, without overlapping partial periods.

---

6. Power BI Time Functions – SAMEPERIODLASTYEAR

Description: Returns a set of dates from the same period in the previous year.
Syntax:

scssCopySAMEPERIODLASTYEAR(<Dates>)

Use Case:
For a year-over-year growth measure:

mathematicaCopyYOY_Growth = 
DIVIDE(
  [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date])),
  CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
)

This function automatically handles leap years and calendar irregularities, making year-over-year comparisons seamless and accurate.

---

7. Power BI Time Functions – TOTALYTD / TOTALMTD / TOTALQTD

Description: Calculates year-to-date (YTD), month-to-date (MTD), or quarter-to-date (QTD) aggregations.
Syntax:

php-templateCopyTOTALYTD(<Expression>, <Dates>[, <Filter>][, <YearEndDate>])

Use Case:
To show cumulative sales for the current year up to today:

iniCopySales_YTD = TOTALYTD([Total Sales], Calendar[Date])

Similarly, use TOTALMTD for month-running totals or TOTALQTD for quarter-running. This instantly conveys trajectory—whether you’re pacing ahead or lagging.

---

8. Power BI Time Functions – ENDOFMONTH / STARTOFMONTH

Description: Returns the last or first date of the month for a given date.
Syntax:

scssCopyENDOFMONTH(<Dates>)
STARTOFMONTH(<Dates>)

Use Case:
Calculate month-end inventory:

csharpCopyInv_EndMonth = CALCULATE(
  [Inventory Value],
  ENDOFMONTH(Calendar[Date])
)

Or start-of-month cash balance:

csharpCopyCash_SOM = CALCULATE(
  [Cash Balance],
  STARTOFMONTH(Calendar[Date])
)

These functions simplify boundary-based calculations, critical for financial and operational reporting.

---

9. Power BI Time Functions – HOUR / MINUTE / SECOND

Description: Extracts the hour, minute, or second component from a datetime value.
Syntax:

scssCopyHOUR(<Time>)
MINUTE(<Time>)
SECOND(<Time>)

Use Case:
Analyze peak hours:

sqlCopySalesByHour = SUMMARIZE(
  Sales,
  HOUR(Sales[Timestamp]),
  "SalesAmt", SUM(Sales[Amount])
)

Use this in visuals to reveal sales patterns by the hour—or even by minute—helping operations staff optimize staffing and promotions.

---

10. Power BI Time Functions – DATEDIFF vs. DATEPART

Description: While DATEDIFF returns intervals between dates, DATEPART (part of SQL but achievable via combinations) can extract specific date parts. In DAX, you mimic DATEPART with YEAR, MONTH, DAY.
Syntax:

sqlCopyYEAR(<Date>)  |  MONTH(<Date>)  |  DAY(<Date>)

Use Case:
Group sales by week day:

sqlCopySalesByWeekday = 
SUMMARIZE(
  Sales,
  WEEKDAY(Sales[Date]),
  "TotalSales", SUM(Sales[Amount])
)

Here, WEEKDAY is analogous to a date part. Combining these functions provides granular control over temporal grouping.

---

Conclusion

By mastering these Power BI Time Functions, you gain the power to build truly dynamic, date-aware reports. From simple “last refresh” timestamps with NOW() to sophisticated year-over-year comparisons using SAMEPERIODLASTYEAR(), each function offers unique value. Start integrating them today to elevate your dashboards from static snapshots to living, breathing analytics. Ready to see these functions in action? Subscribe for step-by-step tutorials and real-world examples!

Read more related articles below.

Use DAX time intelligence functions in Power BI Desktop models
Understanding Time Intelligence with DAX
Importance of Iteration DAX in Power BI
Marketing Campaign Insights Analysis: 7 Powerful Ways to Boost ROI in 2025
5 Powerful Ways to Customize Totals in Power BI Using ISINSCOPE
5 Common DAX Mistakes in Power BI