How to display remaining number by calculating number field against a date field?

[MatE]

I want to display a number of active users for a given month and year in a line chart. My data file got Year, User ID and exit date. I want to display number of active users for each department in a graph. Users who are still active, wont have any value under Exit date field.  Please note that same user can appear in multiple years

Sample data file

Year, User ID, Department, Exit Date 

2018, 1010, ABC

2019, 1010, ABC, 31/01/2019 12:00:00 AM

2018, 2314, ABC,

2018, 5455, ABC, 23/12/2018 12:00:00 AM

2018, 7676, XYZ

2019, 7676, XYZ, 6/03/2019 10:10:00 PM

2019, 6556, XYZ,

2019, 6767, ABC

Thanks. 

[Anonymous]

Hi @MatE ,

I'd like to suggest you to create a unrelated calendar table, then write a measure to use current calendar date to filter on sample table records.

Measure =
VAR currDate =
    MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Year] <= YEAR ( currDate )
                && OR ( [Exit Date] <= currDate, [Exit Date] = BLANK () )
        )
    )

Regards,

Xiaoxin Sheng

[MatE]

Thanks for the reply. How can I group that value by the department? 

[Anonymous · Accepted solution]

HI @MatE ,

You can add department filter to dax formula:

Measure =
VAR currDate =
    MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Year] <= YEAR ( currDate )
                && OR ( [Exit Date] <= currDate, [Exit Date] = BLANK () )
        ),
        VALUES ( Table[Department] )
    )

Regards,

Xiaoxin Sheng