Forum Discussion
Dax formula for datetimeline
- 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:
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
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.