So this is probably a stupid, simple question but I just can't seem to figure it out. I've got a table of people and a table of hours worked for people. There is a one-to-many relationship between the two with the people table ('TMD') on the one side and the hours worked table on the many side. I have a date table in my data set as well. The date table contains the date key, Date, Month, Year, Quarter, and calendar month number.
What I need is for my formula to be able to express the number of hours worked per person for an individual month. What I can get is the total hours worked for each person, but it doesn't filter down to the month level.
The formula that I used that at least gets me this far is:
Hours worked = calculate(sum('Hours Worked'[Regular Hours]),TMD[CommonID])
You can probably see that every single person has the same hours worked for every single month. Truth of the matter is I only have data for Jan and Feb of 2019 in my data set. Originally I only had a relationship between my table of people and my hours worked table. I added a date column in my hours worked table and created a relationship with my date table, but somehow I seem to be further from a solution after doing that.
I figure I need an iterator, sumx instead of sum, but I still don't quite have the hang of DAX and it's just escaping me how to write the line of code I would need that would get PowerBI to add that extra filter. Any help is appreciated.
