Count opened cases by day with "for" loop

[jbetkowska]

Hello,

I have following issue and I don't know how to start writting Dax measure (if it is possible in DAX).

Lets say we have following data table:

ID	opened_at	closed_at
1	opened_date	closed_date
2	opened_date	opened_date

I would like to achieve a chart with dates on X axis - let's say last 30 days that will show count of ID's  that opened_date <= day(i) and closed date > day(i) - meaning I would like to have a chart that shows number of events that were not closed on specific day. I cannot create aggregates in DataBase because it is not mine and I have only RO access. 

Any ideas how to start?

[johnt75 · Accepted solution]

Try

Open cases =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= MaxDate
            && (
                ISBLANK ( 'Table'[End Date] )
                    || 'Table'[End date] > MaxDate
            )
    )
RETURN
    Result

[jbetkowska]

Thank you for the hint. Seems like I have solutions thanks to you!