Creating DAX Measures on Counting Rows with Certain Time Duration from Dynamic Current Time

[Anonymous]

Hi, Guys, 

I would like your help creating a dax measure counting the percent of ID records with a duration over 2 or 3 years. My data includes the service start and stop dates. (See table below). I would like to have a measure couting the number  / percentage of IDs with a service duration over 3 years.  

ID	spstart	spstop	spduration
1	10/1/2021	10/2/2022	367
2	1/12/2020	1/12/2023	1095
3	5/10/2022	6/20/2023	376

My problem is that I need the measure to be dynamic. I would to know on every day from 1/1/2020 (when program starts) unit recent date, how many IDs are in service in that date, and how many IDs have been in service for over 3 years on that date. 

To do this I have created a date table, "Date_new" with date field as [date_new]. The date model has an active join between [date_new] and [spstart], and inactive join between [date_new] and [spstop]. I can create a Dax measure to count the number of IDs in service on a timeline based on [date_new] and use it in the chart and table. But I have difficulties creating the measure to count how many / what percentage have service duration over 3 years. Here is the Dax Measure that is incorrect. 

1. M_%LOS>3yr_incare =
2. var _currentdate = MIN('Date_New'[Date_New])
3. var _date3yrsago = DATEADD(_currentdate, -3, YEAR)
4. var _los3yrs= (spell_final_after2018[spstart]>_date3yrsago)
5. Return
6. CALCULATE(AVERAGEX(FILTER(spell_final_after2018,spell_final_after2018[spstart]<MIN('Date_New'[Date_New]) && spell_final_after2018[spstop]>=MIN('Date_New'[Date_New])), _los3yrs), CROSSFILTER(spell_final_after2018[spstart],Date_New[Date_New],None))

I would appreciate if you can give me some advice on how to create this dynamic measure.

Thanks. 

[FreemanZ]

hi @Anonymous 

not sure if i fully get you, based on this table, say named data:

try to

1) add a calculated table like:

dates = 
CALENDAR(
    MIN(data[spstart]),
    MAX(data[spstop])
)

Do not relate them. 

2) plot a visual with dates[date] column and two measure like:

InServiceCount = 
VAR _date = MAX(dates[Date])
RETURN
    COUNTROWS(
        FILTER(
            data,
            data[spstart]<=_date
                &&data[spstop]>=_date
        )
    )

and 

Pct3YPlus = 
VAR CountInServiceOver3Y=
    CALCULATE(
        [InServiceCount],
        FILTER(
            data,
            DATEDIFF(data[spstart], MIN(data[spstop], MAX(dates[Date])), YEAR)>=3
        )
    )
RETURN
    DIVIDE(CountInServiceOver3Y, [InServiceCount])+0

it worked like: