Forum Discussion
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 | Basic Salary | Allowances | |
| 2020 | Employee 1 | 100 | 50 | |
| 2020 | Employee 2 | 100 | 50 | |
| 2020 | Employee 3 | 100 | 50 | |
| 2020 | Employee 4 | 100 | 50 | |
| 2020 | Employee 5 | 100 | 50 | |
| 2020 | Employee 6 | 100 | 50 | |
| 2020 | Employee 7 | 100 | 50 | |
| 2020 | Employee 8 | 100 | 50 | |
| 2019 | Employee 9 | 100 | 50 | |
| 2019 | Employee 10 | 100 | 50 | |
| 2019 | Employee 11 | 100 | 50 | |
| 2019 | Employee 12 | 100 | 50 | |
| 2019 | Employee 13 | 100 | 50 | |
| 2019 | Employee 14 | 100 | 50 | |
| 2019 | Employee 15 | 100 | 50 | |
| Total | 1500 | 750 | ||
| if I select Year | 2020 | the answer is | 2250 | |
| if I select Year | 2019 | the answer is | 1050 | |
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])))
5 Replies
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!
