Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Vizbaby
Frequent Visitor

Dax 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 active). I want a DAX formulas that will show 

  •  all active members by fiscal year (active in that fiscal year…)
  • new members by fiscal year

For all new members  i just used 

Year(Tablename[startdate column])
i am still confused on how to get all active members, i know i need a formula that will add previous year members to current year members as long as theyre active and 2023 should show the exact number of active members we have in the database.
 
PLS HELP! Thank you
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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_0-1700881925832.png

View solution in original post

7 REPLIES 7
FreemanZ
Super User
Super User

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_0-1700881925832.png

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

Rupak_bi
Resolver II
Resolver II

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? 

Rupak_bi
Resolver II
Resolver II

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.  

NameStart DateEnd Date
James Chapman4/20/20093/17/2019
John Edmark9/21/1998NULL
Barbara Ann Miller2/16/2011NULL
Julia Neal12/1/20058/9/2022
William Gibbs3/1/1988NULL
M. Madeline Adams2/1/19905/31/2004
Lucile Maclennan10/1/199811/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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors