cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imnzh
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.

StartDateEndDateResourceName
29/09/202115/12/2022Merc
24/11/202110/12/2021Ash
30/05/202201/06/2022Irine

 

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.

DateMonthYearCapacityLastDate
29/09/20212021-09830/09/2021
30/09/20212021-09830/09/2021
.....   
15/12/20222022-12815/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.

MonthYearResourceNameTotalCapacity
2021-09Merc16
2021-10Merc168 (21 working days x 8hrs )
2021-11Merc176
2021-11Ash48

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
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.

vangzhengmsft_0-1641446726418.png

Result:

vangzhengmsft_1-1641446849701.png

 

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.

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
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.

vangzhengmsft_0-1641446726418.png

Result:

vangzhengmsft_1-1641446849701.png

 

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.

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
Super User
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

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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