Sum If

[CJ_96601]

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

[parry2k]

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

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]

@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 · Accepted solution]

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]

Thanks.  I will