Forum Discussion

Vizbaby's avatar
Vizbaby
Icon for Helper I rankHelper I
2 years ago
Solved

Dax formula for datetimeline

i have 3 columns. name of members, start date of members and end date of members(some members have blank end dates meaning they're currently active and some with non blank means they're no longer act...
  • FreemanZ's avatar
    2 years ago

    hi  Vizbaby ,

    not sure if i fully get you, try to 

    1) add a calculated table like:

    dates = 
    ADDCOLUMNS(
        CALENDAR(MIN(data[Start Date]), MAX(data[End Date])),
        "Year", YEAR([Date])
    )

    Don't join the dates table with your data table. 

     

    2) plot a table visual with dates[year] column and measures like:

    ActiveMembersCount = 
    VAR _year = MAX(dates[year])
    VAR _list =
    CALCULATETABLE(
        VALUES(data[Name]),    
        FILTER(
            data,
            AND(
                YEAR(data[Start Date]) <= _year,
                YEAR(data[End Date]) >= _year || YEAR(data[End Date]) = BLANK()
            )
        )
    )
    RETURN COUNTROWS(_list)
    NewMembersCount = 
    VAR _year = MAX(dates[year])
    VAR _list =
    CALCULATETABLE(
        VALUES(data[Name]),    
        FILTER(
            data,
            AND(
                YEAR(data[Start Date]) <= _year,
                YEAR(data[End Date]) >= _year || YEAR(data[End Date]) = BLANK()
            )
        )
    )
    VAR _listpre =
    CALCULATETABLE(
        VALUES(data[Name]),    
        FILTER(
            data,
            AND(
                YEAR(data[Start Date]) <= _year-1,
                YEAR(data[End Date]) >= _year-1 || YEAR(data[End Date]) = BLANK()
            )
        )
    )
    VAR _newlist = EXCEPT(_list, _listpre)
    RETURN COUNTROWS(_newlist)

     

    it worked like: