Forum Discussion

meierli's avatar
meierli
Icon for Helper I rankHelper I
1 year ago

Compare dates in two columns and then sum 3rd column

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

 

 

"DAX " "DAX help" 'Need help' 'DAX'
"Power Pivot"
Excel

3 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    1 year ago

    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's avatar
      meierli
      Icon for Helper I rankHelper I
      1 year ago

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.