cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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...

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors