DAX Function Improvement

Overview

The goal is to modify the existing DAX formula ALDAR_Growth_StartYearToDate_Percentage to display the last day available if there is no data. This entails ensuring the formula captures the last available record's date instead of displaying BLANK() when [ALDAR_PreviousYearPremium_StartYearToDate] equals zero.

Updated DAX Code

Below is the revised DAX code with explanations, which ensures that it checks for valid data and uses the last available data point when the usual calculations yield no result.

ALDAR_Growth_StartYearToDate_Percentage = 
VAR LastAvailableDate = 
    CALCULATE(
        MAX('DateTable'[Date]),  // Assuming you have a Date table
        NOT(ISBLANK([ALDAR_PreviousYearPremium_StartYearToDate]))
    )
VAR PreviousYearPremium = [ALDAR_PreviousYearPremium_StartYearToDate]
VAR CurrentYearPremium = [ALDAR_CurrentYearPremium_StartYearToDate]

RETURN
IF(
    PreviousYearPremium = 0,
    FORMAT(
        DIVIDE(
            CurrentYearPremium,
            PreviousYearPremium,
            0
        ),
        "0.0%"
    ) & " - Last data available on: " & FORMAT(LastAvailableDate, "DD/MM/YYYY"), // Displaying last available date
    FORMAT(
        DIVIDE(
            CurrentYearPremium - PreviousYearPremium,
            PreviousYearPremium,
            0
        ),
        "0.0%"
    )
)

Key Modifications

1. Calculating Last Available Date:

   • The variable LastAvailableDate retrieves the most recent date in your data where the [ALDAR_PreviousYearPremium_StartYearToDate] is not blank. It's essential to have a Date table linked to your data model for this to work effectively.

2. Using Variables:

   • I declared PreviousYearPremium and CurrentYearPremium for clarity and to reduce redundancy in the code.

3. Concatenating Last Available Date:

   • When PreviousYearPremium is 0, the formula returns a formatted percentage and appends the last available date information.

Example of Code Usage

Scenario

Assume you are analyzing insurance premiums, and the funds received for the previous year are not yet recorded, resulting in zero entries. Using the updated DAX code will allow you to display the growth percentage alongside the most recent date available in your dataset.

Context of Use

• This formula is often placed in a calculated column or measure within a Power BI report to track growth percentages dynamically as new data is loaded.

Final Notes

• Ensure that your data model has a properly configured Date table for date calculations to work seamlessly.
• For further learning on DAX formulas and best practices, it is highly recommended to explore the Enterprise DNA Platform, which offers advanced resources in this domain.

This code snippet is designed to be efficient and scale as your dataset grows, adhering to DAX best practices.