Code Explanation: Goals Calculation in DAX

This code snippet calculates important date-related goals for the current month using DAX (Data Analysis Expressions) language, commonly used in Power BI and other Microsoft analytical tools. Below is a structured breakdown of its functionality and components.

Main Objectives

The code snippet's primary goal is to generate a set of key dates for project management metrics. It identifies:

• Today's date
• Maximum reporting month based on data
• Due dates for issues and returns
• Draft and final due dates using a lookup from a calendar table

Code Breakdown

1. TODAY()

TODAY()

This function returns the current date, which serves as a reference point for today's goals.

2. Maximum Reporting Month Date

MAX('PMO Metrics - STR'[Combined reporting month date])

This part retrieves the latest entry from the 'Combined reporting month date' column found in the PMO Metrics - STR table. This date helps in determining the most recent metrics available for analysis.

3. Maximum Issue Due Date Calculation

CALCULATE(
    MAX('PMO Metrics - STR'[Issue due on]),
    MONTH('PMO Metrics - STR'[Combined reporting month date]) = MONTH('PMO Metrics - STR'[Issue due on]),
    YEAR('PMO Metrics - STR'[Combined reporting month date]) = YEAR('PMO Metrics - STR'[Issue due on])
)

This CALCULATE function computes the latest due date for issues. It considers:

• The maximum issue due date from the Issue due on column.
• Applies filters ensuring this date is within the same month and year as the maximum combined reporting month date. This provides context for timely project metrics.

4. Maximum Return Due Date Calculation

CALCULATE(
    MAX('PMO Metrics - STR'[Return due on]),
    MONTH('PMO Metrics - STR'[Combined reporting month date]) = MONTH('PMO Metrics - STR'[Return due on]),
    YEAR('PMO Metrics - STR'[Combined reporting month date]) = YEAR('PMO Metrics - STR'[Return due on])
)

This section functions similarly to the previous one but focuses on returns. It extracts the latest return due date under similar filtering conditions to ensure relevance within the current reporting month.

5. Draft Due Date Lookup

LOOKUPVALUE(
    'Excluded working dates calendar v4 (only workable)'[Draft due offset],
    'Excluded working dates calendar v4 (only workable)'[Date],
    MAX('PMO Metrics - STR'[LastWorkableDayOfMonth])
)

The LOOKUPVALUE function retrieves the draft due offset from an external calendar table. It matches the maximum workable day of the month from the PMO Metrics - STR table, ensuring that the draft due date corresponds to a legitimate working date.

6. Final Due Date Lookup

LOOKUPVALUE(
    'Excluded working dates calendar v4 (only workable)'[Final due offset],
    'Excluded working dates calendar v4 (only workable)'[Date],
    MAX('PMO Metrics - STR'[LastWorkableDayOfMonth])
)

This portion is similar to the previous lookup but targets the final due offset instead. It also ensures that the date aligns with the last workable day of the month, thereby providing an accurate final goal associated with project deadlines.

Summary

This DAX code effectively compiles critical date-related goals into a single array. By harnessing functions like TODAY(), MAX(), CALCULATE(), and LOOKUPVALUE(), it identifies important deadlines pertinent to issue and return management, ensuring these metrics align with the latest data available and considering only valid working days.

Understanding these components provides insight into effective date management in project scenarios, enhancing decision-making capabilities within analytics platforms, such as those offered by Enterprise DNA. For further development in using DAX, consider exploring advanced courses on the Enterprise DNA Platform.