cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate summation value for each month based on start date and end date

I have 2 tables, as below.

Table A

This is the main table whereby I have lists of projects and resource name, as well as project start date and end date.

 StartDate EndDate ResourceName 29/09/2021 15/12/2022 Merc 24/11/2021 10/12/2021 Ash 30/05/2022 01/06/2022 Irine

Table B

This is a calendar table I've created, whereby the minimum date is the earliest of starting date of Table A, and maximum is max date of end date of Table A).

Capacity column is the value that I've calculated before based on the day of the date. If the date is on weekend, then the capacity will be zero. If the date is on weekday,then the value will be 8hrs. I also have the last date for each month since I only consider last date during weekdays.

 Date MonthYear Capacity LastDate 29/09/2021 2021-09 8 30/09/2021 30/09/2021 2021-09 8 30/09/2021 ..... 15/12/2022 2022-12 8 15/12/2022

I want to calculate the sum of the capacity for each month only (not cumulative). However, I can't find any possible solutions using DAX. I tried to make the relationship between table A (StartDate col) and B (Date col) but I still cannot get the output, since the value will calculate for all date inside table A.

The expected output should be like this.

 MonthYear ResourceName TotalCapacity 2021-09 Merc 16 2021-10 Merc 168 (21 working days x 8hrs ) 2021-11 Merc 176 2021-11 Ash 48

1 ACCEPTED SOLUTION
Community Support

Hi, @imnzh

Try to do it with Power Query.

You can refer to the following steps to get the results from Table A.

I have attached the M code below.

Result:

M code:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUN7DUNzIwMlTSUTI01Tc0AnGMgBzf1KJkpVgdoBITfUNDuBIDqBIQx7E4A6zC2EDfwBSmz8BQ38AMxvEsysxLVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, ResourceName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type text}, {"EndDate", type text}, {"ResourceName", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"StartDate", type date}, {"EndDate", type date}}, "en-GB"),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
#"Inserted Day of Week" = Table.AddColumn(#"Changed Type1", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <> 0 and [Day of Week] <> 6)),
#"Inserted Year" = Table.AddColumn(#"Filtered Rows", "Year-Month", each Text.From(Date.Year([Date]))&"-"&Text.From(Date.Month([Date]))),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Year", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Capacity", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"ResourceName", "Year-Month"}, {{"TotalCapacity", each List.Sum([Capacity]), type nullable number}})
in
#"Grouped Rows"``````

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Hi, @imnzh

Try to do it with Power Query.

You can refer to the following steps to get the results from Table A.

I have attached the M code below.

Result:

M code:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUN7DUNzIwMlTSUTI01Tc0AnGMgBzf1KJkpVgdoBITfUNDuBIDqBIQx7E4A6zC2EDfwBSmz8BQ38AMxvEsysxLVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, ResourceName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type text}, {"EndDate", type text}, {"ResourceName", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"StartDate", type date}, {"EndDate", type date}}, "en-GB"),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
#"Inserted Day of Week" = Table.AddColumn(#"Changed Type1", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <> 0 and [Day of Week] <> 6)),
#"Inserted Year" = Table.AddColumn(#"Filtered Rows", "Year-Month", each Text.From(Date.Year([Date]))&"-"&Text.From(Date.Month([Date]))),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Year", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Capacity", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"ResourceName", "Year-Month"}, {{"TotalCapacity", each List.Sum([Capacity]), type nullable number}})
in
#"Grouped Rows"``````

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

I know I might be late, but thanks to your solution, I managed to do like what I'm expected. Your answer really works the way I intended it to be. Thank you so much for your help. Really appreciate that.

Your solution's quite detailed, following the pbix file attached. I could do all the steps properly without encounter any problem. Thanks to you.

Super User

@imnzh , try a measure like

Hours = CALCULATE(Sumx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(realted(Date[Capacity]))),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

refer for joins

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

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

sorry but this isnt working. It displays "the column date[capacity] isnt exist or doesnt have any relationship to any table available in the current context."

Disclaimer: I have changed the table name and column based on my database, and the relationship exists between Table B (calendar table) and Table A like I mentioned in the post. But somehow the measure is not working for me.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors