Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
For all new members i just used
Solved! Go to 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:
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
Thank you so much! it worked
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.
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?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
24 | |
22 |