SUM LOOKUPVALUE - Attempting to sum units with multiple criteria

[PowerMyBI]

Hi Power BI forum,

I have a vast table of transaction data and I'm hoping to find the total utilised time per staff per month in a table that looks similar to the one below. I'm struggling to find the right formula to use in my calculated column. Appreciate any suggestions.

StaffName	UnitsLogged	IsUtilised	MonthRecorded	CalculatedColumn
Sally	5	True	202010	5
Sally	2	False	202010	5
Bob	6	True	202010	9
Bob	3	True	202010	9
Bob	7	False	202010	9
Sally	4	True	202009	11
Sally	7	True	202009	11
Sally	7	False	202009	11
Bob	5	False	202009	8
Bob	8	True	202009	8

Thanks!

[amitchandak · Accepted solution]

@PowerMyBI , Try a new column like

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ="True"),[UnitsLogged])

if IsUtilised is a boolean

CalculatedColumn = sumx(filter(Table, [StaffName] =earlier([StaffName]) && [MonthRecorded] =earlier([MonthRecorded]) && [IsUtilised] ),[UnitsLogged])

[PowerMyBI]

@amitchandak that works perfectly! Thank you for help