Calculate hours declared in advance using a measure

[LucaBSST]

Hello everyone! Hope you are doing great

I have some difficulties with a business problem. I'm actually creating a report showing hours done by employees on specific phases of a job order.
I have two fact tables to do that. The first table shows the hours that must be done to conclude the job. These hours are linked to the phase (example of the structure below). The expected closing date is the date at which the phase is expected to be finished.

Job order	Phase	Expected closing date	Hours to do
TELECOM	Statistical study	28 February 2022	20
TELECOM	Test	28 February 2022	40
TELECOM	Construction	28 February 2022	100
TELECOM	Design	5 March 2022	60

The second table shows the hours declared by employees for the phases (example of the structure below).

Job order	Phase	Employee	Date of execution	Hours done
TELECOM	Test	M. Scott	6 February 2022	16
TELECOM	Design	D. Schrute	15 February 2022	10
TELECOM	Construction	S. Hudson	15 February 2022	32
TELECOM	Statistical study	J. Halpert	20 February 2022	8

The idea is that I need a DAX measure that gives me the sum of the hours expected to be done next month ( March in the example ) but declared this month ( February in my example ). Still following the example, my measure should give me 10 hours. In other words, I simply need a measure to get the work that has been done in advance by the employees. if the expected closing date of the phase from the first table is after this month, I sum all the hours done in the second table.

The only "key" I have between these two tables is the phase. My initial idea was to first use LOOKUPVALUE (using the phase as a common column) to get the closing date and then use an IF statement saying "if the expected closing date of the phase from the first table is after this month, I sum all the hours done for that phase in the second table.

I usually find the solution but I can't get my way around it this time! Hope my explanations were clear and thank you in advance for your help :)!

Luca

A piece of the model

[Anonymous]

HI @LucaBSST,

It seems like you used the date field as a relationship key to mapping two table records but they should not be suitable for these records analysis.
Id' like to suggest you take a look at the following link to know how to build the relationship between multiple fields.

Relationship in Power BI with Multiple Columns - RADACAD

Then you can simply use the relationship to calculate the diff between two table records and get the cumulative results.

Regards,

Xiaoxin Sheng

[littlemojopuppy]

Hi @LucaBSST can you provide some sample data to work with?