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

Anonymous — Sat, 22 Apr 2023 04:49:17 GMT

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.

M_%LOS>3yr_incare =
var _currentdate = MIN('Date_New'[Date_New])
var _date3yrsago = DATEADD(_currentdate, -3, YEAR)
var _los3yrs= (spell_final_after2018[spstart]>_date3yrsago)
Return
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.

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

FreemanZ — Sat, 22 Apr 2023 14:40:00 GMT

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:

topic Creating DAX Measures on Counting Rows with Certain Time Duration from Dynamic Current Time in DAX Commands and Tips

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

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