Forum Discussion

clubspec's avatar
clubspec
Icon for Helper III rankHelper III
3 years ago
Solved

Tricky SUM with two tables

Hi Gurus, I have two tables like in the picture.  There is a relationship between 'Calendar'[Date] and 'Asset Register'[Start Date]. Now I want to do a sum for [Monthly Depreciation] from the Asset...
DAX
sum
  • PaulOlding's avatar
    PaulOlding
    3 years ago

    Hi clubspec 

    You can use the CROSSFILTER function to remove the relationship just for this measure.

    Something like this:

    Monthly Dep = 
VAR _MaxDate = MAX('Date'[Date])
VAR _Result = 
CALCULATE(
    SUM('Asset Register'[Monthly depreciation]),
    CROSSFILTER('Date'[Date], 'Asset Register'[Start Date], None),
    'Asset Register'[Start Date] <= _MaxDate,
    'Asset Register'[End Date] >= _MaxDate
)
RETURN
    _Result

     

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
3 years ago

Hi clubspec 

You can use the CROSSFILTER function to remove the relationship just for this measure.

Something like this:

Monthly Dep = 
VAR _MaxDate = MAX('Date'[Date])
VAR _Result = 
CALCULATE(
    SUM('Asset Register'[Monthly depreciation]),
    CROSSFILTER('Date'[Date], 'Asset Register'[Start Date], None),
    'Asset Register'[Start Date] <= _MaxDate,
    'Asset Register'[End Date] >= _MaxDate
)
RETURN
    _Result