Time Intelligence in Power BI: PYTD, PQTD, PMTD, and PWTD
- Bernard Kilonzo
- Jan 30
- 3 min read
Overview
Time is the backbone of business analysis. Whether you're tracking sales momentum, monitoring operational efficiency, or comparing performance across periods, the ability to interpret data through a temporal lens is what transforms dashboards from static reports into strategic decision engines. Power BI’s time intelligence functions - particularly PYTD (Prior Year-to-Date), PQTD (Prior Quarter-to-Date), PMTD (Prior Month-to-Date), and PWTD (Prior Week-to-Date) - give analysts the tools to answer the questions leaders ask most often: How are we doing compared to before? Are we improving? Where are we falling behind?
These functions do more than calculate differences; they provide context. They help you align performance with seasonality, uncover trends masked by short-term fluctuations, and build visuals that tell a compelling story about progress over time. For anyone building robust, insight-driven Power BI models, mastering these time intelligence patterns is not optional - it’s foundational.
This article unpacks each of these functions, explaining what they are, and how they fit into a modern analytics workflow. By the end, you’ll have a clear, practical understanding of how to leverage PYTD, PQTD, PMTD, and PWTD to elevate your reporting and deliver sharper, more actionable insights.
These time intelligence functions depend on a date table (a dedicated table in your data model that contains every single date in a continuous range, plus useful attributes like year, quarter, month, week, and day details.) to understand, navigate and manipulate time correctly.
I have shared a step-by-step guide on how to create a date table in Power BI using DAX in the link below.
(In this example, I have created my date table running from the start of my facts table to the current date)
Next, I have connected the date table with my facts table in the model view and created the base measure “Total sales” - aggregating all the sales as shown below.
1. Prior-Year-to-Date (PYTD)
PYTD returns the cumulative value from the start of the previous year up to the same day/month as the current filter context or current date.
Syntax
2. Prior-Quarter-to-Date (PQTD)
PQTD returns the cumulative value from the start of the previous quarter up to the same relative day within that quarter.
Syntax
3. Prior-Month-to-Date (PMTD)
PMTD returns the cumulative value from the start of the previous month up to the same day number in that month.
Syntax
4. Prior-Week-to-Date (PWTD)
PWTD returns the cumulative value from the start of the previous week up to the same weekday.
Syntax
See the resulting values
Conclusion
Mastering PYTD, PQTD, PMTD, and PWTD isn’t just about writing clever DAX formulas - it’s about giving your reports the temporal depth they need to drive meaningful decisions. These functions anchor your metrics in context, reveal patterns that raw numbers can’t show, and help stakeholders understand not only what is happening, but why it matters.
As you integrate these time intelligence patterns into your Power BI models, you start building dashboards that feel alive - responsive to seasonality, aligned with business rhythms, and capable of telling a clear story about performance over time. Whether you're comparing year‑to‑date progress, evaluating quarter‑over‑quarter momentum, or tracking week‑to‑week shifts, these tools form the backbone of a mature analytics practice.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!!