Code Extender

PYTD Counts Extension with Percentage Change

This DAX extension calculates Year-to-Date distinct constituent counts and adds functionality to show the percentage change compared to the previous year, ensuring robust error handling and clear variable naming for better readability.

Prompt

PYTD Counts = 
VAR CurrentDates = FILTER( VALUES(vwDimDate[ActualDate]),vwDimDate[ActualDate]<= 'val vwSmallGiftsRevenueSummary'[Last Date]) 
VAR RESULT = CALCULATE([Distinct Constituent Count],SAMEPERIODLASTYEAR(DATESYTD(CurrentDates,"6/30"))) 
RETURN RESULT

Answer

Code Extension for PYTD Counts

Existing Code Snippet Analysis

The existing code is a DAX expression used within a Power BI model. It calculates the Year-to-Date (YTD) distinct constituent count based on a filtered date range. The structure of the code includes:

Variables: CurrentDates stores filtered date values, and RESULT calculates the distinct count using a DAX measure named [Distinct Constituent Count].
Functions: Uses FILTER, VALUES, CALCULATE, SAMEPERIODLASTYEAR, and DATESYTD.

Objective

The goal is to extend the existing DAX code to incorporate additional functionality. Specifically, we will add a calculation to show the percentage change compared to last year for the distinct constituent count.

Extension Implementation

The following code will integrate the percentage change calculation while preserving the original logic.

Extended Code

PYTD Counts =
VAR CurrentDates = FILTER(VALUES(vwDimDate[ActualDate]), vwDimDate[ActualDate] <= 'val vwSmallGiftsRevenueSummary'[Last Date])
VAR CurrentCount = CALCULATE([Distinct Constituent Count], DATESYTD(CurrentDates, "6/30"))
VAR LastYearCount = CALCULATE([Distinct Constituent Count], SAMEPERIODLASTYEAR(DATESYTD(CurrentDates, "6/30")))
VAR PercentageChange = 
    IF(
        NOT ISBLANK(LastYearCount), 
        DIVIDE(CurrentCount - LastYearCount, LastYearCount, 0), 
        BLANK()
    )
RETURN 
    CurrentCount & " (Percent Change: " & FORMAT(PercentageChange, "0.00%") & ")"

Code Explanation

CurrentCount: This variable calculates the distinct constituent count for the current period using YTD dates.
LastYearCount: This variable captures the distinct count for the same period last year, enabling comparison.
PercentageChange: Computes the percentage change using the DIVIDE function, ensuring to handle cases where LastYearCount is zero or blank.
Return Statement: Combines both the current count and the percentage change into a single formatted string.

Best Practices Adhered

Variable Naming: Descriptive names are used to ensure clarity of purpose.
Error Handling: The use of IF and ISBLANK to avoid errors during division ensures robust error handling.
Code Organization: Logical structuring of operations helps in enhancing readability and maintainability.

This extension efficiently adds the desired functionality while maintaining the original intent of the code. For further mastery in DAX and Power BI, consider exploring courses available on the Enterprise DNA Platform to sharpen your skills.