grouping date columns in to months then grouping row data

[Joannec]

Hi,

I'm new to Power BI and was hoping someone may be able to help. I have a resource utilisation / capacity table showing me, by day (over a 1 year period), who is working on what project / on leave / available to work etc. I would like to group the column by month and then breakdown how mnay days have ben spent on each project / leva etc p[er month. In excel I would create a named range and then do a countif based on the row value, but how do I do this in power Bi to give me a report and can drill donw on?

I have the following:

resource	cont / perm / ecm	rate (£'s)	01/10/2017	02/10/2017	03/10/2017	04/10/2017
resource 1	perm	100		proj 1	Available	Available
resource 2	perm	100		proj 2	proj 2	proj 2
resource 3	perm	100		proj 3	proj 3	Available
resource 4	contract	100		H	H	Available
resource 5	perm	100		proj 2	proj 2	proj 2
resource 6	perm	100		H	H	H

and would like to see:

Oct 2017   1 day spent on proj 1, 6 days on proj2, 4 days showing as available (so I can then calculate cost implcations etc)

Nov 2017  5 days on proj 1, 2 days on proj 2 etc

Thank you

[fhill · Accepted solution]

Data as your posted is technically 'pivoted' to have a data element "Month" horizontal on a table.  Power BI has a great 'Un-Piviot Columns' Feature to convert you data horizontal again.  (Only select the Date columns, or use the advanced options in drop down.)

Once you've un-piviotred the data, you can rename your Month column, and now you can easily create a table by Month and Value (Resource?) and then add the column again in Count form.

FOrrest

[Joannec]

Thank you so much!