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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |