Forum Discussion
Vizbaby
Helper I
2 years agoDax 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...
- 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:
FreemanZ
Super User
2 years agohi 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
Helper I
2 years agoThank you so much! it worked