Forum Discussion

CJ_96601's avatar
CJ_96601
Helper 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
Super 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
Helper 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
    Super 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
      Helper V
      6 years ago

      Thanks.  I will

    • CJ_96601's avatar
      CJ_96601
      Helper 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])
          )
      )