Forum Discussion

CJ_96601's avatar
CJ_96601
Icon for Helper V rankHelper V
6 years ago
Solved

Sum If

Please advise on how to sum all data in two columns if year  is less than or equal to year selection.  Table and calendar has relationship by date.   Below is the same data:     Year BN Bas...
Need Help
  • CJ_96601's avatar
    CJ_96601
    6 years ago

    Sharing:

     

    This one works:

     

    TotalComp =

       CALCULATE(SUMX(CnB_D,CnB_D[Comp]+CnB_D[Comp1]+CnB_D[Comp2]+CnB_D[Comp3]+CnB_D[Comp4]),


             FILTER (
            ALL ( 'CnB_D'[Hire Date].[Year]),
            'CnB_D'[Hire Date].[Year] <= MAX ( 'zCalendar'[Year])
        )
    )   
CJ_96601's avatar
CJ_96601
Icon for Helper V rankHelper V
6 years ago

Thanks, 

 

Tried your solution, it calculates but randomly skips few rows in my excel file (with 6000 rows to calculate).

 

All dates are ok

 

Any idea why?

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

CJ_96601 very hard to say without looking at that why that would be.You have to validate at your end or send the pbix file 

  • CJ_96601's avatar
    CJ_96601
    Icon for Helper V rankHelper V
    6 years ago

    Thanks.  I will

  • CJ_96601's avatar
    CJ_96601
    Icon for Helper V rankHelper V
    6 years ago

    Sharing:

     

    This one works:

     

    TotalComp =

       CALCULATE(SUMX(CnB_D,CnB_D[Comp]+CnB_D[Comp1]+CnB_D[Comp2]+CnB_D[Comp3]+CnB_D[Comp4]),


             FILTER (
            ALL ( 'CnB_D'[Hire Date].[Year]),
            'CnB_D'[Hire Date].[Year] <= MAX ( 'zCalendar'[Year])
        )
    )