Calucation between imported table and summarized & cross joined table

[Miawto]

Hi, 

I need to calculate available hours pr. employee per team per month e.g. like this: 

Table A: Imported table with project allocations pr. day pr. employee e.g.,

Date	Project	Initials	Hours
January 1st 2024	Alpha	ABC	8
January 1st 2024	Delta	DEF	8
Januart 2nd 2024	Beta	ABC	4

Table B: A summarize table with the employee capacity per month e.g., 

Initials	Month	SumCapacity
ABC	January	143
ABC	February	135
DEF	January	154

I got help creating this table here, if you need details

I've tried with measurements and a similar new table as table B, but I'm not getting anywhere close to reality.

Do you have any suggestions? 

[v-tangjie-msft · Accepted solution]

Hi @Miawto ，

Thanks AmiraBedh  for the quick reply. I have some other thoughts to add and am not sure if I have understood your needs correctly, if I have misunderstood please give the expected results and processing logic in a follow up reply.

(1)We can create two columns.

Month Column = FORMAT([Date],"mmmm")

Available hours = 
var _SumCapacity=CALCULATE(SUM('Table B'[SumCapacity]),FILTER('Table B',[Month]=EARLIER('Table A'[Month Column]) && 'Table B'[Initials]=EARLIER('Table A'[Initials])))
var _hour=CALCULATE(SUM('Table A'[Hours]),FILTER('Table A',[Date]<=EARLIER('Table A'[Date]) && [Initials]=EARLIER('Table A'[Initials])))
RETURN _SumCapacity-_hour

(2)Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[AmiraBedh]

What do you want to achieve exactly?

[Miawto]

Hi @AmiraBedh 

I'd like to be able to visualize availability in a matrix like the first example I give in the text  where I describe what I need. 
If that doesn't answer your question then please let me know and I'll try to rephrase it 

[AmiraBedh]

Can you please provide clear input data ? is your Table B summarized in PBI or you are importing it like that?

[Miawto]

Hi @AmiraBedh 

Table B is summarized in PBI - I had help with this (see the solution here). It's summarized based on a date table and a imported table with employee data. 

Can't give the specific tables incl. datapoints 'cause it has personal data - that's why I've given examples of the data in my initial request and links to the solution. 

Hope this helps 🙂