topic Re: Compare dates in two columns and then sum 3rd column in DAX Commands and Tips

Compare dates in two columns and then sum 3rd column

meierli — Wed, 23 Oct 2024 20:00:26 GMT

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

aduguid — Wed, 23 Oct 2024 22:05:56 GMT

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 — Thu, 24 Oct 2024 16:37:14 GMT

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

Anonymous — Fri, 25 Oct 2024 02:48:44 GMT

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.