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

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi clubspec 

please try

Total Depreciation =
VAR CurrentDate =
MIN ( 'Calendar'[Date] )
VAR T1 =
CALCULATETABLE ( 'Asset Register', ALL ( 'Calendar' ) )
VAR T2 =
FILTER (
T1,
'Asset Register'[Start Date] <= CurrentDate
&& 'Asset Register'[End Date] >= CurrentDate
)
RETURN
SUMX ( T3, 'Asset Register'[Monthly Depreciation] )