DAX - kind of Calculate(SUM ... condition 1 and use input from another) or SUMX of 2 tables

[FelixB]

Hi all,

I have 2 tables with the fields

• Kilometers
  • Vehicle
  • Date
  • Km on the date
• Activities
  • Vehicle
  • ActivityID
  • FinishDate

The tables are connected via a vehicle table. I cannot connect the date table with the activities table.

I would like to sum the kilometers when the Kilometers(Date) > Activities(Finish Date) for Activity = 2.

Can you help me, please? What other information do you need?

[foodd]

How to Get Your Question Answered Quickly  meaning, please add your work-in-progress Power BI Desktop file, and source data in Excel format.

[punitkhatri]

Hii, @FelixB according to the information you have provided this formula should work if all the relationships are proper.
Formula =
CALCULATE(SUM('Kilometers'[Km on the date]),
'Kilometers'(Date) > 'Activities'(Finish Date)  && 'Activities'(ActivityID) = "2")

Please let me know if this works or not, if it doesn't work please provide a sample data to work on which will make it easy for me to help.

[FelixB]

The error message: The expression contains columns from multiple tables but only columns from a signle table can be used in a True/False expression that is used as a table filter expression.

[punitkhatri]

Please provide a sample data. So that I can see what exactly is the problem.

[FelixB]

I was not able to upldate example data but the solution is:

Output =

VAR A = CALCULATE(MAX(Activities[FinishDate]), FILTER(Activities, Activities[ActivityID] = 2))

RETURN CALCULATE(SUM(Kilometers[Kilometer]), FILTER(Kilometers, Kilometers[Date] > A))