Distributing total duration for a task throughout multiple months

[jabbajuice08]

I need to create a bar graph that needs to add up all the total durations within each month. So each bar represents a different month and year combination (we have our own specific accounting calendar- JAN 2022, FEB 2022, etc, etc) that I use as a reference and have within my report).  

So for example, a task starts 1/1/2022 and ends 3/5/2022 and has a total duration of 64 days in the 'TOTAL DURATION' column.

For our accounting calendar, JAN 2022 would be from 1/1/2022-1/28/2022 and FEB 2022 would be from 1/29/22-1/28/22. My calendar table has the DATELABEL, DATESTART, and DATEFINISH columns. So that task would have 28 days within JAN (and then weekends would have to be subtracted), 31 days in FEB, and 5 days in March. The same thing would go for every single task, and the bar graphs would have those overall counts for each accounting month/year. 

I have no idea how to tackle this problem. I work in an environment where I can't share my powerbi report, so unfortunately I can't share any sample report. If anyone would help, it would be much appreciated!

[v-jingzhang · Accepted solution]

Hi @jabbajuice08 

I have attached a sample file at bottom for your reference. It has two examples, one including weekends and the other excluding weekends. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[jabbajuice08]

@v-jingzhang Thank you! This worked perfectly for me. Only question...if my calendar table works like this: 

where I have ranges of dates to signify which month it falls in, instead of using a calendar where it has each, individual date, how would I go about using those same formulas? 

[v-jingzhang]

Hi @jabbajuice08 

Your date table is not a standard date table, so I would suggest that you convert it into a standard date table which will make further calculation easier. Do You Need a Date Dimension? - RADACAD

You can use Power Query Editor to transform the current table. First add a custom column with 

List.Dates([MonthStartDate],Duration.Days([MonthEndDate]-[MonthStartDate])+1,#duration(1,0,0,0))

Then expand this list column to new rows. You will transform the table into below. Rename the column "Custom". This column has individual dates in every range. 

Best Regards,
Community Support Team _ Jing