Forum Discussion

jabbajuice08's avatar
jabbajuice08
Frequent Visitor
3 years ago
Solved

Distributing total duration for a task throughout multiple months

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!

Need Help

3 Replies

    • jabbajuice08's avatar
      jabbajuice08
      Frequent Visitor
      3 years ago

      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's avatar
        v-jingzhang
        Community Support
        3 years ago

        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