Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
Thanks for the reply. How can I group that value by the department?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |