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

[imnzh]

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

[v-angzheng-msft · Accepted solution]

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"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"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),
    #"Added Custom1" = Table.AddColumn(#"Inserted End of Month", "Capacity", each 8),
    #"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.

[imnzh]

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.

[amitchandak]

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

[imnzh]

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.