DAX Calculation - Date Hierarchy Issue

[zkeller]

Hi,

I followed this tutorial to create a measure that utilized a date table ('Financial Calendar.csv') to create a record of open cases over time.  The 'Active Cases (Dynamic)' measure screenshotted below is working as expected.  However, I'm trying to create a non-cumulative version of that metric and I'm noticing that the summarizations within the date hierarchy aren't working as hoped.  For example, the month's total, rather than summing the cases within the month, is taking the last day's value, and if there is no applicable value then it is showing as blank.  I'm hoping you can help me update the 'Closed Cases Test (Dynamic)' measure such that it will show the number of cases closed each day (rather than a cumulative, running count) that also gets aggregated correctly within the date hierarchy.

Thanks for any help you can provide!

Active Cases (Dynamic) =
VAR currentDate =
    MAX ( 'Financial Calendar'[Date] )
VAR firstHireDate =
    MIN ( 'Finance Service Management Case'[Case Opened (Date)] )
VAR activeEmployees =
    CALCULATE (
        COUNTROWS ( 'Finance Service Management Case' ),
        ALL ( 'Financial Calendar' ),
        'Finance Service Management Case'[Case Opened (Date)] <= currentDate,
        // include employees hired to date
        'Finance Service Management Case'[Case Closed (Date)] > currentDate
            || ISBLANK ( 'Finance Service Management Case'[Case Closed (Date)] ) // include employees whos contract is active
    )
VAR result =
    IF (
        YEAR ( currentDate ) <= YEAR ( TODAY () )
            && currentDate >= firstHireDate,
        activeEmployees
    )
RETURN
    result

Closed Cases Test (Dynamic) =
VAR currentDate =
    MAX ( 'Financial Calendar'[Date] )
VAR firstHireDate =
    MIN ( 'Finance Service Management Case'[Case Closed (Date)] )
VAR activeEmployees =
    CALCULATE (
        COUNTROWS ( 'Finance Service Management Case' ),
        ALL ( 'Financial Calendar' ),
        'Finance Service Management Case'[Case Closed (Date)] = currentDate,
        NOT ( ISBLANK ( 'Finance Service Management Case'[Case Closed (Date)] ) )
    )
VAR result =
    IF (
        YEAR ( currentDate ) <= YEAR ( TODAY () )
            && currentDate >= firstHireDate,
        activeEmployees
    )
RETURN
    result

Semantic Model

[zkeller]

Hi Greg,

It does seem very similar, however, I'm not really sure how that helps me.  Are you suggesting that I replace the measure formula I'm currently using with what you linked?  I really just need to understand why the date hierarchy is not aggregating this particular measure.

[Greg_Deckler]

@zkeller Seems like a variation of Open Tickets. Open Tickets - Microsoft Fabric Community