Dax formula for datetimeline

[Vizbaby]

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 active). I want a DAX formulas that will show 

•  all active members by fiscal year (active in that fiscal year…)
• new members by fiscal year

For all new members  i just used 

Year(Tablename[startdate column])

i am still confused on how to get all active members, i know i need a formula that will add previous year members to current year members as long as theyre active and 2023 should show the exact number of active members we have in the database.

PLS HELP! Thank you

[FreemanZ · Accepted solution]

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:

[Vizbaby]

Hello FreemanZ,

Thank you so much for helping with my data, the formula worked. However, i was just told to show my table by fiscal year as well. I created a fiscal year table and filter, but when i add the active member and new member measure, it doesnt work. If i try to filter the table using fiscal year (I created a fiscal year table), it gives me wrong figures. please how do i fix this? 

Also, i tried using a card to show all active members by fiscal card (using the formula you gave me), thinking that would be easier doesnt work. 

sorry for dusturbing and thanks again for your help

[Vizbaby]

Thank you so much! it worked

[Rupak_bi]

Hi, 
I dont have PBI desktpo access now. However yo may try following.
create a disconnected date table: you may use calenderauto()

now make measure of active member = calculate(max(name), end date = blank() || year(end date)>year(max(new date table[date])))

now use the new calender table year in either slicer or rwo in matrix table to get your desired result. dont create any relationship. 

Else will simulate it tomorrow in PBI.

[Vizbaby]

 active member = calculate(max(name), end date = blank() || year(end date)>year(max(new date table[date])))

please what columns do u mean by the highlighted words? do you mean the column name "end date" on my data? 

[Rupak_bi]

to calculate active member, just create a measure to flag as below

if(table [end date] =blank(),"inactive","active")

now use this flag measure to filter the report.
you may also use below formula directly as well

calculate(max(table,[name]),table,[end date]<>blank())

try any of these based on how you want to represent ot in the visual.

for new members

calcualte(max(table,[name],start date>fiscal year start date && satr date<=fiscal year end date.

If this not works, please share sample data

[Vizbaby]

Thank you so much for the filter, however i am still struggling to find the count for all active members per year. Example of what im looking for, lets use the year 2005.  

Name	Start Date	End Date
James Chapman	4/20/2009	3/17/2019
John Edmark	9/21/1998	NULL
Barbara Ann Miller	2/16/2011	NULL
Julia Neal	12/1/2005	8/9/2022
William Gibbs	3/1/1988	NULL
M. Madeline Adams	2/1/1990	5/31/2004
Lucile Maclennan	10/1/1998	11/29/2021

the dax formula for all active members;  2005 should show Julia Neal who joined in 2005 but left in 2022 and also william Gibbs, though he joined in 1988 since he doesnt have an end date, he was still active in 2005.