DAX - referencing row header in a formula

I need your help in creating a DAX measure that would help me resolving the following problem:

I have a table that shows case number, creation date and closure date:

case_number   creation_date    closure_date
CA001         4/15/2020        4/19/2020
CA002         4/17/2020        4/20/2020
CA003         4/19/2020        4/21/2020
CA004         4/19/2020        4/20/2020

I have a pivot with various measures where row headers are consecutive days from a related Calendar table. One of the columns I need is a number of active cases in given day (number of cases that were created before or the same day as the context date, AND closed after the context date). The expected outcome would be like this:

date        open_cases
4/13/2020   0
4/14/2020   0
4/15/2020   1
4/16/2020   1
4/17/2020   2
4/18/2020   2
4/19/2020   3
4/20/2020   1
4/21/2020   0

in regular Excel, the formula that calculates the same would be =COUNTIFS(table_cases[creation_date],"<="&[@date],table_cases[closure_date],">"&[@date]), but in my case this will be a part of a bigger pivot table/dashboard and I need to write a measure in DAX that would calculate this.

I would appreciate your help in this matter.

Best regards, Michal

Solution

There are a variety of ways to read the current context. You can use MAX or VALUES or SELECTEDVALUE (if available). These don't all behave exactly the same, so use whichever is appropriate for your situation.

open_cases =
VAR RowDate = MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNT ( table_cases[case_number] ),
        table_cases[creation_date] <= RowDate,
        table_cases[closure_date] > RowDate
    )

Here, MAX is evaluated within the filter context, so it should return the value in the current row (or the maximal date for subtotals and grand totals).