DAX: Calculate Relatively Last Month, Quarter, and Year – A Step-by-Step Guide

Are you tired of manually calculating relative dates in Power BI? Do you want to take your data analysis to the next level? Look no further! In this article, we’ll show you how to use DAX to calculate relatively last month, quarter, and year. With these techniques, you’ll be able to create robust and dynamic reports that impress your stakeholders.

Understanding the Problem

Let’s face it, calculating relative dates can be a real pain. You need to consider the current date, the relative period, and the date range. It’s easy to get lost in the calculations, and before you know it, you’re drowning in a sea of formulas.

That’s where DAX comes in. With its powerful calculation engine, you can create formulas that calculate relative dates with ease. But, before we dive into the solutions, let’s define the problem.

The Requirements

We want to calculate the following relative dates:

• Last month
• Last quarter
• Last year

We’ll use these calculations to create measures that can be used in our reports. But, before we start, let’s clarify some assumptions:

• We’re using a date table with a continuous range of dates.
• We’re using Power BI as our reporting tool.

The Solutions

Now that we’ve defined the problem, let’s dive into the solutions. We’ll create three measures, each calculating a different relative date.

Calculate Relatively Last Month

The first measure calculates the last month. We’ll use the `EOMONTH` function to get the last day of the previous month and then subtract one day to get the last day of the current month.

Last Month =
VAR CurrentDate = TODAY()
VAR LastMonthDate = EOMONTH(CurrentDate, -1)
VAR LastDayOfLastMonth = LastMonthDate - 1
RETURN
    LastDayOfLastMonth

Let’s break down the formula:

• `VAR CurrentDate = TODAY()`: We define a variable `CurrentDate` that returns the current date using the `TODAY()` function.
• `VAR LastMonthDate = EOMONTH(CurrentDate, -1)`: We define a variable `LastMonthDate` that returns the last day of the previous month using the `EOMONTH` function with an offset of -1.
• `VAR LastDayOfLastMonth = LastMonthDate – 1`: We define a variable `LastDayOfLastMonth` that returns the last day of the previous month by subtracting one day from `LastMonthDate`.
• `RETURN LastDayOfLastMonth`: We return the `LastDayOfLastMonth` variable as the final result.

Calculate Relatively Last Quarter

The second measure calculates the last quarter. We’ll use the `QUARTER` function to get the current quarter and then subtract one quarter to get the last quarter.

Last Quarter =
VAR CurrentDate = TODAY()
VAR CurrentQuarter = QUARTER(CurrentDate)
VAR LastQuarter = CurrentQuarter - 1
RETURN
    'Date'[Date] <= EOMONTH(TODAY(), -3 * LastQuarter) && 'Date'[Date] > EOMONTH(TODAY(), -3 * (LastQuarter + 1))

Let’s break down the formula:

• `VAR CurrentDate = TODAY()`: We define a variable `CurrentDate` that returns the current date using the `TODAY()` function.
• `VAR CurrentQuarter = QUARTER(CurrentDate)`: We define a variable `CurrentQuarter` that returns the current quarter using the `QUARTER` function.
• `VAR LastQuarter = CurrentQuarter – 1`: We define a variable `LastQuarter` that returns the last quarter by subtracting one from `CurrentQuarter`.
• `RETURN ‘Date'[Date] <= EOMONTH(TODAY(), -3 * LastQuarter) && 'Date'[Date] > EOMONTH(TODAY(), -3 * (LastQuarter + 1))`: We return a filter that selects dates within the last quarter. We use the `EOMONTH` function to get the last day of the quarter and then filter the dates using the `AND` operator.

Calculate Relatively Last Year

The third measure calculates the last year. We’ll use the `YEAR` function to get the current year and then subtract one year to get the last year.

Last Year =
VAR CurrentDate = TODAY()
VAR CurrentYear = YEAR(CurrentDate)
VAR LastYear = CurrentYear - 1
RETURN
    'Date'[Date] >= DATE(LastYear, 1, 1) && 'Date'[Date] < DATE(CurrentYear, 1, 1)

Let's break down the formula:

• `VAR CurrentDate = TODAY()`: We define a variable `CurrentDate` that returns the current date using the `TODAY()` function.
• `VAR CurrentYear = YEAR(CurrentDate)`: We define a variable `CurrentYear` that returns the current year using the `YEAR` function.
• `VAR LastYear = CurrentYear - 1`: We define a variable `LastYear` that returns the last year by subtracting one from `CurrentYear`.
• `RETURN 'Date'[Date] >= DATE(LastYear, 1, 1) && 'Date'[Date] < DATE(CurrentYear, 1, 1)`: We return a filter that selects dates within the last year. We use the `DATE` function to create a date range and then filter the dates using the `AND` operator.

Using the Measures

Now that we've created the measures, let's use them in our report. We'll create a table that displays the sales amount for each relative date.

Relative Date	Sales Amount
Last Month	= CALCULATE(SUM('Sales'[Sales Amount]), DATESBETWEEN('Date'[Date], Last Month, Last Month))
Last Quarter	= CALCULATE(SUM('Sales'[Sales Amount]), FILTER('Date', Last Quarter))
Last Year	= CALCULATE(SUM('Sales'[Sales Amount]), FILTER('Date', Last Year))

In this example, we use the `CALCULATE` function to calculate the sales amount for each relative date. We filter the dates using the measures we created earlier.

Conclusion

In this article, we've shown you how to calculate relatively last month, quarter, and year using DAX. With these measures, you can create robust and dynamic reports that impress your stakeholders.

Remember to adapt these formulas to your specific use case and date table structure. Happy reporting!

Additional Resources

Want to learn more about DAX and Power BI? Check out these additional resources:

• DAX Reference
• Power BI Learn
• Power BI DAX Forum

We hope you found this article helpful. Happy reporting!

Frequently Asked Question

Get ready to unlock the secrets of DAX calculation! Below are some frequently asked questions about calculating relatively last month, quarter, and year using DAX.