How to calculate multiplication results from two fact tables, based on date within a period

[Iris_]

Hi PowerBI community,

I am a beginner with PowerBI and would like to implement a calculation with condition based on two fact tables.

My current data model looks as below:

The goal here is to get the multiplication results of Rates[Bill Rate] * Actual_Planned[Actual_Hour]

based on the condition of Actual_Planned[Date] within the period defined in Rates table, i.e. Rates[Start] <= Actual_Planned[Date] <= Rates[End]

Below methods have been tried out but without success:

1. Dax: create a measure as below

Rate = CALCULATE(SUM(RELATED(Rate[Bill Rate])*'Actual_Planned[Actual_Hour]),
                 FILTER(ALL('Actual_Planned'),'Actual_Planned'[Date] <= RELATE('Rate'[END]) && 
                                              'Actual_Planned'[Date] >= RELATED('Rate'[START])))

However, it shows the columns cannot be found the the parameter type is not correct

2. Power Query: merge the two table by Enterprise ID & Role.

However, it looks like Power Query does not allow filter out the rows with column value comparison, i.e. Rates[Start] <= Actual_Planned[Date] <= Rates[End]

I am looking very forward to your input. Any suggestions are welcome. 

Thanks a lot for your time.

[adudani]

hi @Iris_ ,

try this:

Rate = CALCULATE(SUMX('Actual_Planned'),(RELATED(Rate[Bill Rate])*'Actual_Planned[Actual_Hour]),
                 FILTER(ALL('Actual_Planned'),'Actual_Planned'[Date] <= RELATED('Rate'[END]) && 
                                              'Actual_Planned'[Date] >= RELATED('Rate'[START])))

Appreciate a thumbs up if this helps.

Please accept this as the solution if the query is resolved.

[piotr_gor]

Should you itereate over "Master_Employee" Table?

Try [...]SUMX('Master_Employee')[...]

[Iris_]

Hey @adudani 

thanks a lot for help to check! Unfortunately, it still shows the error with RELATED(...).

I am not sure if there can be an issue with the relationships in the data model. 🤔