How to use periods to create monthly reports

[Anonymous]

Dear all,

I am a newbie in PowerBI and stuck on a challenge. 

I have two datasets about demand and capacity. 

Capacity Dataset has the teams, a period (StartDate, EndDate) and the number of people(FTE) we have during that period.

The Demand Dataset has the Activity, a period for implementation(StartDate, EndDate), the team needed and number of people(FTE) needed from the team.

I would like to create reports on these datasets 

• how many people we have each month from each team
• the demand requirements per month per team
• if the capacity is enough to meet the demand per month

I appreciate your help to solve this.

[AlB]

@Anonymous 

What exactly is the expected result, for years and for Totals?

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Unfortunately, my organization does not let me to share externally. 

If we think of available people all year, the total is correct unless there is a change during the year. 

But when there is a change such as in FINCON BA APAC row, then it adds up the numbers. This double way of calculation makes the data unreliable.

We can say that the Total column is not meaningful when we think as number of people working during the year.

But this creates problems when I want to calculate available peoplehours, by multiplying AvailableFTEs by 18days. The total in that case should be the total of each column in the row, but is is as shown below.

Is there any way to solve this with measures? I do not want to use Crossjoin, but it seems like I have to.

Thanks

[Anonymous]

Testing to send message

[AlB]

@Anonymous 

Please provide an example of what exactly is not working and idealy share the pbix (or one with dummy data that reproduces the problem)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

It seems like I can not add the pbix file here.

Here is my data “Capacity”

I created a table of MyCalendar using

MyCalendar = CALENDAR(MIN(Capacity[StartDate]), MAX(Capacity[EndDate]))

Then I added a measure to capacity table 

AvailableFTEs = 
VAR minDate_ = MIN ( MyCalendar[Date] )
VAR maxDate_ = MAX ( MyCalendar[Date] )
RETURN
CALCULATE (
SUM ( Capacity[FTE] ),
Capacity[StartDate] <= maxDate_,
Capacity[EndDate] >= minDate_
)

When I put the Months and Available FTEs on a report the monthly capacity is calculated correctly but Years and Totals are not working.

And Year vs Resource Type is not correct.

[AlB · Accepted solution]

Hi @Anonymous 

You're asking for quite a lot. I'll help out with the first requirement.

1. You can best create a date table DateT (no relationships)

2. Place DateT[Year], DateT[Month] and Capacity[Team] in a table visual

3. Create a measure and place it in the visual. You'd need to clarify what to do in cases where  a resource is available on a fraction of the month only.

AvailableFTEs =
VAR minDate_ = MIN ( DateT[Date] )
VAR maxDate_ =  MAX ( DateT[Date] )
RETURN
    CALCULATE (
        SUM ( Capacity[FTE] ),
        Capacity[StartDate] <= maxDate_,
        Capacity[EndDate] >= minDate_
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

 I realized with this method Monthly capacity is calculated correctly, but the year total is not working. Is there any method to correct this behavior of measures?

[Anonymous]

Thanks a lot, This is quite easy and to do point. 

[amitchandak]

@Anonymous , see if My HR blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4

else

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.