DAX Formula for Daily Volume of the Prior Month's Day

Formula

The following DAX formula calculates the daily volume for the corresponding day in the previous month:

DailyVolumePriorMonth = 
VAR CurrentDate = SELECTEDVALUE('Date'[Date])
VAR PriorMonthDate = EOMONTH(CurrentDate, -1) + DAY(CurrentDate)
RETURN
CALCULATE(
    SUM('Sales'[Volume]),
    'Date'[Date] = PriorMonthDate
)

Explanation

Components of the Formula

1. CurrentDate:

   • This variable retrieves the currently selected date from the Date table using the SELECTEDVALUE function. This function ensures that we are working with a single date context.

2. PriorMonthDate:

   • This variable calculates the corresponding date in the prior month.
   • EOMONTH(CurrentDate, -1) returns the last date of the previous month.
   • Adding DAY(CurrentDate) adjusts the date to the same day of the month (e.g., if today is March 15, it returns February 15).

3. CALCULATE:

   • This function changes the context in which data is evaluated.
   • Inside the CALCULATE, the SUM function calculates the total volume from the 'Sales' table where the date matches the PriorMonthDate.

Logic

• The formula effectively shifts the current date back by one month while retaining the day of the month.
• It then filters the sales data to compute the sum of volumes specifically for that date from the previous month.

Practical Example

Assuming today's date is March 15, 2023, the CurrentDate would be March 15, 2023. The PriorMonthDate would then calculate as:

• February 15, 2023.

If the 'Sales' table contained the following data:

The formula would output 100, which represents the total volume on February 15, 2023.

Conclusion

This DAX formula allows for the calculation of the daily volume from the prior month, facilitating time-based comparisons in reporting and analytics. For further enhancements and concepts in DAX, consider exploring the courses available on the Enterprise DNA Platform to deepen your understanding.