Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.