If you want to compare the sales up to a particular day and compare it with the previous period (month, quarter, or year) but up to that particular day in that period, then it is called previous month-to-date, previous quarter-to-date, and previous year-to-date. These calculations can be more helpful than comparing with the entire period last month because if this month is still not full, then comparing with a full period won’t give you a close comparison point. In this article and video, I’ll show you how you can calculate these using DAX in Power BI.
Video
Sample Model
The sample model I am using is a data model like the one below.
In the model above, I am not using the default/built-in date table in Power BI. I have used the DimDate as a custom date table and marked it as a Date table. This is necessary to be done for the calculations below to work. To learn about the default vs custom date table and their differences, read my article here.
Year-to-Date, Quarter-to-Date, and Month-to-Date using DAX
I have previously explained how to write a YTD (Year-to-Date), a QTD (Quarter-to-date), and an MTD (month-to-date) using DAX in Power BI. However, just as a quick review, here are the calculations again;
Sales Measure;
Sales = SUM(FactInternetSales[SalesAmount])
Sales YTD (Year-to-date) measure;
Sales YTD =
CALCULATE(
[Sales],
DATESYTD(DimDate[FullDateAlternateKey])
)
Sales QTD (Quarter-to-date) measure;
Sales QTD = CALCULATE(
[Sales],
DATESQTD(DimDate[FullDateAlternateKey])
)
Sales MTD (Month-to-date) measure;
Sales MTD = CALCULATE(
[Sales],
DATESMTD(DimDate[FullDateAlternateKey])
)
To learn how the YTD, QTD, and MTD calculations work, please read my article here.
Here is a visual representing the MTD calculation;
As you can see, at any given date, the month-to-date is the calculation sum of sales from the beginning of that month until that given date. You can also see that the accumulation restarts when the new month (August in the screenshot above) starts.
Now let’s see how we can get the previous MTD calculations.
Previous Month-to-date Calculation
In DAX there are multiple functions that you can use to get to the previous date period, I explained some of the most common functions in this article. One simple way to calculate the previous MTD is to just calculate the current MTD but for the previous period. DateAdd can give us that calculation on a daily basis. ParallelPeriod would bring the entire previous period, so I won’t use that in this context. To learn more about the differences between ParallelPeriod and DateAdd read my article here.
Here is the calculation for the previous MTD;
Sales MTD Previous = CALCULATE(
[Sales MTD],
DATEADD(DimDate[FullDateAlternateKey],-1,MONTH)
)
And you can see how it works in our sample report;
As you can see, at any given date, the MTD calculates the sum of sales from the 1st of that month to that date. In the screenshot above, the value presented is for the sum of sales from 1st to the 9th of August 2005. And the Previous MTD calculation calculates the sum of sales from 1st to 9th of the previous month (July 2005). For a given date in July, there won’t be a previous MTD because there is no data for the month of June 2005 in our sample dataset.
Previous Quarter-to-date Calculation
The same approach can be used to calculate the previous QTD as below;
Sales QTD Previous = CALCULATE(
[Sales QTD],
DATEADD(DimDate[FullDateAlternateKey],-1,QUARTER)
)
And here is the example output;
For the given date of 14th of December 2005, the QTD gives you the sum of sales from 1st of October to 14th of December 2005. and the previous QTD gives you exactly the same period in the previous quarter (from 1st of July to 14th of September 2005). This calculation uses the same DATEADD function to get the previous period, the only difference is the period is changed to QUARTER in the expression.
Previous Year-to-date Calculation
And finally, the previous YTD calculation will be as below;
Sales YTD Previous =
CALCULATE(
[Sales YTD],
DATEADD(DimDate[FullDateAlternateKey],-1,YEAR)
)
And here is the example output;
As you can see in the screenshot above, the YTD sales of 1st of Jan until 16th of Oct of 2007 are presented beside the previous YTD sales of 1st of Jan until 16th of Oct 2006.
Other Period-based calculations
If you are interested in other period-based calculations, I encourage you to read a couple of articles below;
Summary
As you see in this article, calculating the previous month-to-date, quarter-to-date, and year-to-date can be done simply by calculating the original value (YTD, QTD, or MTD) over the previous period using a function such as DATEADD. There are, of course, other methods of calculating this as well. Like everywhere else in DAX and Power BI, your calculations are dependent on the context of the report and visualization, Remember to write your calculation in a way that performs correctly for the specific report and visual you want to present it.
I provide training and consulting on Power BI to help you to become an expert. RADACAD team is helping many customers worldwide with their Power BI implementations through advisory, consulting, architecture design, DAX support and help, Power BI report review and help, and training of Power BI developers. If you need any help in these areas, please reach out to me.
Here are links to some of the articles mentioned in this blog that would help you to understand the concept of this article easier;
- Power BI Date Dimension; Default or Custom? Is It Confusing?
- Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date
- DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question
- Previous Dynamic Period DAX Calculation
- Week to Date Calculation in Power BI with DAX
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.