Forum Discussion

Miawto's avatar
Miawto
New Member
2 years ago
Solved

Calucation between imported table and summarized & cross joined table

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 Initia...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

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

Miawto's avatar
Miawto
New Member
2 years ago

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 🙂