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

     

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

Hi clubspec 

I understand that your data, at least from picture, for Calendar / Date Table seems find (one day / date as unique and that table as Date table featured in PowerBI).

Still to "leverage" PowerBI features for calculating  / reporting MTD amounts your data should be on respective granularity, meaning one single date: day or monthly level only. It will be great if you already have or you could have that level of data per asset item. It is a bit confusing seeing your column "Monthly Depreciation" and next two dates like period. It could be that you need to use PQ transformation or somehow prepare that to be with just one monthly dates / amounts, before any DAX. I do not know it by heart :). I hope this help.