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
- Vizbaby2 years ago
Helper I
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.