Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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...
FreemanZ's avatar
FreemanZ
Super User
3 years ago

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: