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:
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:
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