Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
clubspec
Helper III
Helper III

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 Register table, however use the [Year] and [Month] from the 'Calendar' table as the row element to display the result.  The requirement is simple, sum rows in column 'Asset Register'[Monthly Depreciation] if 'Calendar'[Year] and [Month] (e.g. 1 Jan 23) is between the 'Asset Register'[Start Date] and [End Date].

I have tried so many dax but couldn't work out the correct answer (also in the picture below).  I know the relationship cause part of the problem but I cannot remove it because there are other measures using it.

Please help...

clubspec_0-1684470231182.png

 

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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_0-1684513255208.png

 

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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] )

PaulOlding
Solution Sage
Solution Sage

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_0-1684513255208.png

 

Thank you so much Paul, it is working 🙂

some_bih
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors