Help with data between two dates

[Anonymous]

Hello,

I am fairly new to BI and wondering if anyone might be able to offer me some advice on a report I am trying to finish.

I am connected to our Dynamics 365 service, pulling in data for projects.   Each one has a start data and end date as well as the number of monthly labor hours estimated each will consume for the duration of the project.

I have another table with total amount of man power we have in our labor pool.  

I would like to create a visual that shows the total monthly labor hours consumed (from all projects taking place during a given month) shown against what we actually have available.    

Any guidance would be greatly appreciated.   Thank you!

[Anonymous]

Hi @Anonymous ,

Based on my understanding of your requirements, I think a clustered bar chart should be able to show this. If you could provide an example dataset, I may be able to help a bit more.

Thanks

[Anonymous]

Hi there.  Thank you for your reply.   I have included sample data below:

Project A

start date: 2/1/2020

end date: 4/30/2021

monthly hours: 238

Project B

start date: 2/1/2020

end date: 11/30/2020

monthly hours: 159

Total available manpower: 960 hours

[Anonymous]

I would like to be able to see all allocated hours for a given calendar month 

[Anonymous]

Is this similar to what you are looking for?

[Anonymous]

Looking to aggregate all consumed hours across all projects in a given month against available.

[Anonymous]

I understand now. As far as I know, there is no simple way to this. Most visualizations only have one date input which means it doesn't know the other months the project continues on for. The only solution I can think of it to have a row for each month the project is occuring and have the monthly hours in another column for each row.

Data Format

 Below is how it would display and the user can select the month and year with the slicer.

Power BI display

[Anonymous]

OK thank you!  I understand the concept.  Is there a formula I could use to achieve this without having the manually enter them?  I habe many more than two so it would be nice to have it automated as more are entered.

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You could try this simple way

step1:

Use CALENDARAUTO Function to create a dim date table as below:

Date = ADDCOLUMNS(CALENDARAUTO(),"YearMonthNo",YEAR([Date])*100+MONTH([Date]),"Year Month", YEAR ( [Date] )& " " & FORMAT ( [Date], "mmmm" ) )

Step2:

Use this logic to create a new table

New table = 
FILTER (
    GENERATE (
        'Table',
        SUMMARIZE ( 'Date', 'Date'[Year Month], 'Date'[YearMonthNo] )
    ),
    [YearMonthNo]
        >= YEAR ( [Start Date] ) * 100+ MONTH ( [Start Date] )
        && [YearMonthNo]
            <= YEAR ( [end date] ) * 100 + MONTH ( [end date] )
)

Result:

and you could also have a look this post that similar to your case.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

here is my sample pbix file, please try it.

Regards,

Lin