Compare dates in two columns and then sum 3rd column

[meierli]

I am using Power Pivot in EXCEL not Power BI.

Trying to compare dates with an if statement from two columns and when the date matches I want to sum a 3rd columns.

Here is the DAX formula I have, which is not working. What do I need to change to be able to compare the dates and then sum up the values?

I also created date tables for the two date columns. Didn't work either.

MM CM:=IF(VALUE('Vendor Trend'[CAP Date Paid])=VALUE('Vendor Trend'[CAP Date Aff]), SUM([TOTAL MM]))

MM CM:=IF(VALUE('Calendar'[Date])=VALUE('Calendar_Dt Aff'[Date]), SUM([TOTAL MM]))

[Anonymous]

Hi @meierli 

Thank you very much aduguid for your prompt reply.

Try this:

MM CM = 
CALCULATE(
    SUM('Vendor Trend'[TOTAL MM]),
    FILTER(
        'Vendor Trend',
        'Vendor Trend'[CAP Date Paid] = 'Vendor Trend'[CAP Date Aff]
    )
)

MM CM = 
CALCULATE(
    SUM('Vendor Trend'[TOTAL MM]),
    FILTER(
        'Calendar',
        'Calendar'[Date] = 'Calendar_Dt Aff'[Date]
    )
)

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Try this measure

MM CM :=
CALCULATE(
    SUM('Vendor Trend'[TOTAL MM]),
    'Vendor Trend'[CAP Date Paid] = 'Vendor Trend'[CAP Date Aff]
)

or this one if you're using a calendar table

MM CM :=
CALCULATE(
    SUM('Vendor Trend'[TOTAL MM]),
    'Calendar'[Date] = 'Calendar_Dt Aff'[Date]
)

[meierli]

Thank for your suggestion. But I got enclosed error. Any idea why?