Generating values based on column

[Arnab_Dutta]

Hello,

I am trying to do the following:
Data table A:
Name
Project
Start-Date
End-Date
AllocationHours
DailyHoursBurnRate

Data table B:
Month
AllocatedHours (Virtual)

I want to compute AllocatedHours as a total burndownvalue for each month which should be equal to SUM of [(No.of working days assigned in month)*DailyHoursBurnRate] .

I can compute the allocated working days for the month for each row in table A, but then cannot work out how to implement a calculation within a SELECT statement for matching rows.

Any suggestions would be appreciated!

thanks.

[Arnab_Dutta]

I have got around this problem by changing the table B structure to having rows for each day of the year.

[Aleksi]

First you need to calculate the [No.of working days assigned in month]*[DailyHoursBurnRate] as you have probably done already with the virtual column. Then you can sum those values with the formula like…

SUM(SELECT(TableA[VirtualColumn],MONTH(EOMONTH([Start-Date],0))=[_THISROW].[MONTH])).

The result with the MONTH() expression is a number, so your Month column in TableB needs to be number as well. Though you would probably need to take care of the year as well if you have data from different years.