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])
        )
    )   
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

CJ_96601 try this measure

Sum by Year = 
CALCULATE (  
    SUM ( SomeData[Basic Salary] ) + 
    SUM ( SomeData[Allowances] ), 
    FILTER ( 
        ALL ( 'Calendar'[Date] ), 
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) 
    ) 
)

 

I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

  • 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