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 would like to have a measure couting the number  / percentage of IDs with a service duration over 3 years.  

IDspstartspstopspduration  
110/1/202110/2/2022367  
21/12/20201/12/20231095  
35/10/20226/20/2023376  

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. 

 

 

 

 

 

1 Reply

  • 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: