Forum Discussion

Vizbaby's avatar
Vizbaby
Icon for Helper I rankHelper I
2 years ago
Solved

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
  • FreemanZ's avatar
    FreemanZ
    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:

     

     

7 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    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:

     

     

    • Vizbaby's avatar
      Vizbaby
      Icon for Helper I rankHelper I
      2 years ago

      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

       

       

       

       

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    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

    • Vizbaby's avatar
      Vizbaby
      Icon for Helper I rankHelper I
      2 years ago

      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. 

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    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.

    • Vizbaby's avatar
      Vizbaby
      Icon for Helper I rankHelper I
      2 years ago

       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?