Resource sheet based on skill codes in Power BI

[Anonymous]

Hi,

I'm fairly new to power BI and only just starting to get the hang of some of the DAX formulas. I am currently trying to figure out, how I could build a resource report in Power BI.

We track resource by skill code e.g. Multiple staff numbers having the same skill code. I am trying to chart the number of active head count for each month (would need to work with Power BI date hirarchy) per skill code.

Skill codes would have a hierarchy of function group (Department) - skill code  - staff name

This is easy enough to do in excel using COUNTIFS, but would need to use the dates as the column headers.

I would like to build the same graph in Power BI, while retaining both the date and the skill code hierarchy as filters.

Please see sample data below:

Would really appreciate some help!

Thank you

[TeigeGao]

Hi @Anonymous ,

In your scenario, we can create a calendar table and two measures.

Firstly, we can use the following DAx query to create a calendar table, the start date and end date depends on your requirement.

Table = CALENDAR(DATE(2018,1,1),DATE(2019,2,1))

You can also use the following one to create it: 

Table = CALENDAR(MIN(Table1[Start Date ]),MAX(Table1[End Date ]))

Then we can create two measures for Engineer, Technician:

Measure = CALCULATE(COUNT(Table1[Staff Name ]),FILTER('Table1',Table1[Skill Code ] = "Engineer" && Table1[Start Date ] <= MIN('Table'[Date]) && Table1[End Date ] >= MIN('Table'[Date])))

Measure 2 = CALCULATE(COUNT(Table1[Staff Name ]),FILTER('Table1',Table1[Skill Code ] = "Technician" && Table1[Start Date ] <= MIN('Table'[Date]) && Table1[End Date ] >= MIN('Table'[Date])))

Best Regards,

Teige

[Anonymous]

Hi Teige,

Thank your for your reply, it's helped me quite a lot regarding how to structure a filter formula, but doesn't seem to fix the entire problem. I have tied charting the measures in Power BI and couldn't get it to work ....

Why are you using the MIN() function for the start and end date comparison? Doesn't MIN('Table'[Date]) only produce a single date reference which is the earliest date in the date table?

What I am trying to do is have the calendar date as the "running variable" and then check the list of people for each date at a time if they are active headcount based on their start and end date and split it by skill code. This would give me an active headcount chart over time.

The bit I am struggling with is how to create such a r"running variable" logic in Power BI. Would I have to create a calculated column in the time table instead that would allow me to evaluate each date at a time? Does that mean I can still use skill code slicers in the graph?

I hope that makes sense?

Thank you for your help,

Eva  

[vanessafvg]

@Anonymous  are you able to provide a snippet of data?

[Anonymous]

Hi,

Thank you for your reply. Below is dropbox link for sample excel data set including active headcount table using countifs. Let me knowif power BI file is more helpful, however didn't get as far as with Excel.

https://www.dropbox.com/s/2prdetp0wcnaxmz/Sample.xlsx?dl=0

Thank you,