DAX Code Analysis for Fiscal Year Calculation

Analysis of User Concerns

The user indicated that the provided DAX code snippet is not returning the correct values for the current fiscal year. This suggests that there may be issues related to filter context—specifically, how 'Fiscal Year' and 'Date' filters are applied in the calculations.

Code Inspection

Upon inspection, the following areas appear to be of concern:

1. Use of ALLSELECTED with FILTER: The use of ALLSELECTED may lead to unexpected results in the context of the cumulative calculation since it still respects filters from the current context. This could be causing the code to return values outside of the desired fiscal year.

2. Filter Context Handling: The handling of filter context for both fiscal year and fiscal period may not effectively isolate just the current fiscal year due to potential conflicts arising from the nested filters.

3. Logical Conditions: The conditional logic to sum the various types of wRVUs should ensure that it correctly evaluates the condition for the selected component.

Code Rectification

Original Code

Dynamic Cumulative wRVUs = 
VAR CurrentFiscalYear = 
    CALCULATE(
        MAX('Fiscal_Year'[Fiscal Year Numeric]),
        REMOVEFILTERS('Date'),
        REMOVEFILTERS('Fiscal_Year'),
        FILTER(
            ALL('Billed_Activity'),
            'Billed_Activity'[Post_Period] <= MAX('Billed_Activity'[Post_Period])
        )
    )
VAR CurrentFiscalPeriod = MAX('Date'[Fiscal Period])
RETURN
SUMX(
    VALUES('wRVU Components'[Component]),
    VAR CurrentComponent = 'wRVU Components'[Component]
    VAR ComponentTotal = 
        CALCULATE(
            IF(
                ISBLANK(SUM(Billed_Activity[wRVU_Funds_Flow])) && 
                ISBLANK(SUM(Billed_Activity[wRVU_Funds_Flow_APP])) &&
                ISBLANK(SUM(Billed_Activity[wRVU_Non_Funds_Flow])) &&
                ISBLANK(SUM(Billed_Activity[wRVU_No_Dept_Revenue])),
                BLANK(),
                SWITCH(
                    TRUE(),
                    CurrentComponent = "Funds Flow wRVUs", SUM(Billed_Activity[wRVU_Funds_Flow]),
                    CurrentComponent = "Funds Flow APP wRVUs", SUM(Billed_Activity[wRVU_Funds_Flow_APP]),
                    CurrentComponent = "Non Funds Flow wRVUs", SUM(Billed_Activity[wRVU_Non_Funds_Flow]),
                    CurrentComponent = "No Department Revenue wRVUs", SUM(Billed_Activity[wRVU_No_Dept_Revenue]),
                    0
                )
            ),
            FILTER(
                ALLSELECTED('Date'),
                'Date'[Fiscal Period] <= CurrentFiscalPeriod &&
                MAX('Fiscal_Year'[Fiscal Year Numeric]) = CurrentFiscalYear
            )
        )
    RETURN
    ComponentTotal
)

Corrected Code

Dynamic Cumulative wRVUs = 
VAR CurrentFiscalYear = 
    CALCULATE(
        MAX('Fiscal_Year'[Fiscal Year Numeric]),
        REMOVEFILTERS('Date'),
        REMOVEFILTERS('Fiscal_Year'),
        FILTER(
            ALL('Billed_Activity'),
            'Billed_Activity'[Post_Period] <= MAX('Billed_Activity'[Post_Period])
        )
    )
VAR CurrentFiscalPeriod = MAX('Date'[Fiscal Period])
RETURN
SUMX(
    VALUES('wRVU Components'[Component]),
    VAR CurrentComponent = 'wRVU Components'[Component]
    VAR ComponentTotal = 
        CALCULATE(
            IF(
                ISBLANK(SUM(Billed_Activity[wRVU_Funds_Flow])) && 
                ISBLANK(SUM(Billed_Activity[wRVU_Funds_Flow_APP])) &&
                ISBLANK(SUM(Billed_Activity[wRVU_Non_Funds_Flow])) &&
                ISBLANK(SUM(Billed_Activity[wRVU_No_Dept_Revenue])),
                BLANK(),
                SWITCH(
                    TRUE(),
                    CurrentComponent = "Funds Flow wRVUs", SUM(Billed_Activity[wRVU_Funds_Flow]),
                    CurrentComponent = "Funds Flow APP wRVUs", SUM(Billed_Activity[wRVU_Funds_Flow_APP]),
                    CurrentComponent = "Non Funds Flow wRVUs", SUM(Billed_Activity[wRVU_Non_Funds_Flow]),
                    CurrentComponent = "No Department Revenue wRVUs", SUM(Billed_Activity[wRVU_No_Dept_Revenue]),
                    0
                )
            ),
            FILTER(
                ALL('Date'),   // Changed from ALLSELECTED to ALL to remove current context filters
                'Date'[Fiscal Period] <= CurrentFiscalPeriod &&
                'Fiscal_Year'[Fiscal Year Numeric] = CurrentFiscalYear // Direct condition reference
            )
        )
    RETURN
    ComponentTotal
)

Comments on Corrections

1. Change from ALLSELECTED to ALL:

   • Issue Addressed: Using ALLSELECTED may pull in other contexts not relevant to the current fiscal year, leading to incorrect sums.
   • Impact: Using ALL ensures that only the specified context for 'Date' is taken, which directly aligns with our goal of isolating the financial computations to the current fiscal year.

2. Direct Reference to 'Fiscal Year' in FILTER:

   • Issue Addressed: The original filtering logic for the fiscal year was using a MAX function inside the filter condition, which may not properly filter the current fiscal year due to potential context overlaps.
   • Impact: The direct condition 'Fiscal_Year'[Fiscal Year Numeric] = CurrentFiscalYear grants explicit filtering, enhancing clarity and precision.

These modifications should ensure the code returns the correct cumulative wRVUs for the current fiscal year as intended.