Skip to Content

Microsoft PL-300: What DAX Expression Creates a Rolling 31-Day Sales Total in Power BI?

By: Author Alex Lim

Posted on

Categories Exam

Learn the correct DAX expression to calculate a rolling 31-day sales total in Power BI using the CALCULATE and DATESBETWEEN functions on related Sales and Date tables.

Table of Contents

Question

You have a Power BI model that contains two tables named Sales and Date. The Sales table relates to the Date table by using a many-to-one relationship. The Sales table contains the following columns:

  • Date
  • Product
  • SalesAmount

You need to create a DAX measure for a rolling 31-day sales total that will return the total sales amount for a selected date and the previous 30 days.

Which DAX expression should you use?

A. CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Date[Date], -30, DAY))
B. CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max(‘Date'[Date])-30, Max(‘Date'[Date])))
C. CALCULATE(SUM(Sales[SalesAmount]), DATESMTD(Date[Date]))
D. CALCULATE(SUM(Sales[SalesAmount]), DISTINCTCOUNT(Date[Date]) = 31)

Answer

The correct DAX expression to create a rolling 31-day sales total measure is:

B. CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max(‘Date'[Date])-30, Max(‘Date'[Date])))

Explanation

The CALCULATE function is used to modify the context in which a calculation is performed. In this case, we want to sum the SalesAmount column from the Sales table but only for a specific date range.

The first argument to CALCULATE is the expression we want to calculate, which is SUM(Sales[SalesAmount]). This sums up the SalesAmount column from the Sales table.

The second argument to CALCULATE specifies the filter context. We use the DATESBETWEEN function to define a date range that includes the selected date and the previous 30 days.

DATESBETWEEN takes three arguments:

  1. The Date column from the Date table, which the Sales table has a many-to-one relationship with
  2. The start date of the range, which is calculated by taking the maximum date in the Date table and subtracting 30 days
  3. The end date of the range, which is just the maximum date in the Date table

So this expression sums the SalesAmount for all dates between the selected date and 30 days prior, providing a rolling 31-day total.

The other options are incorrect:

  • Option A uses DATEADD which doesn’t define a range, it only shifts the date back 30 days
  • Option C uses DATESMTD which gives month-to-date totals, not a 31-day rolling window
  • Option D tries to filter for 31 distinct dates, but doesn’t define the right 31 day window

In summary, option B provides the correct DAX expression using CALCULATE, SUM, and DATESBETWEEN to calculate a rolling 31-day sales total based on the selected date. The many-to-one relationship between the Sales and Date tables enables this date-based filtering and aggregation.

Microsoft PL-300 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft PL-300 exam and earn Microsoft PL-300 certification.