Forum Discussion
Dynamic Status for pragmatic report
I currently have 2 data sets
List if employees with training date and employee ID
List of work done with date of work done and employee ID (Note: single employee has done multiple jobs in one day)
What i need to do is make a dynamic list of 'trained' employees which shows which month they have been active and which months they have been inactive. Whilst also being able to pull a list of inactive employees in a certain month.
i can do this quite easily in Excel using filters and IF/Count formulas however struggling to merge the 3 dynamics in Power BI
This is the output i need so i can create a table graph showing active/inactive employees and a seperate visual highlighting the inactive ones
2 Replies
the only way i can think of doing this is by merging a list of employees to a generic list of dates.
so if i had 365 days and 100 employees i would expect to have a table containing 36500 rows from here i can assign a 'Active' 'Inactive' custom column on a daily basis and then count unique in powerBi to condense into a monthly graph however this does not represent the cummulative increase in staff over the same period as staff trained in November would show as inactive in October but this is not the case
please help
Hi Anonymous ,
I think we need to create a date table and then create dax to determine the date of work fields that meet the conditions for the date. But if you want to judge by month, you need a separate formula for each month and then add it to the table visual.
If there is still confusion, please refer to the following blog to provide relevant test data (remove sensitive information) so that I can answer you as soon as possible.How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
