Mapping of phases to dates

[MalyMajo]

Hello,

I have a table of actual hours submitted to system and I would like to map the phases according to a custom table.

Example of actual hours table:

Resource Name	Date	Hours
Resource1	5/1/2022	1
Resource1	6/30/2022	5
Resource2	6/30/2022	4

Example of phases table:

Start	End	Phase
5/1/2022	6/15/2022	Phase1
6/16/2022	8/31/2022	Phase2

What is the most efficient approach to map these tables so in final I would see which hours belong under which phase?

Should I merge somehow the phases table to calendar table in power query? Or using DAX? 

Please note that the table with actuals has 10,000+ rows, above is just a simplified example.

Thanks,

Majo

[DataInsights · Accepted solution]

@MalyMajo,

Here's a DAX solution. No relationship exists between ActualHours and Phases.

Calculated column in ActualHours:

Phase = 
VAR vDate = ActualHours[Date]
VAR vTable =
    FILTER ( Phases, vDate >= Phases[Start] && vDate <= Phases[End] )
VAR vResult =
    MAXX ( vTable, Phases[Phase] )
RETURN
    vResult

[MalyMajo]

Hello,

as per my understanding, calculated columns are less effective than measures.

But in this case it is not possible because result is a string, or is it?

Thanks,
Majo
--------------
EDIT: Never mind, I got it quickly. 🙂

Actuals:=
VAR SumActuals = SUM(Actuals[Hours])
VAR vDate = MAX(Actuals[Date])
VAR vTable= FILTER ( Phases, vDate >= Phases[Start] && vDate <= Phases[End] )
VAR vResult = MAXX ( vTable, Phases[Phase] )
RETURN
CALCULATE(SumActuals,Phases[Phase]=vResult)