Sum using filter Weeknum

[Qotsa]

Hi,

Trying to Sum [Charged_Hrs] if [StartDate] = [Weeks]. 

[Weeks] uses Weeknum -> Weeks = WEEKNUM(vw_ShowChargesAndPayForVisits[iDate],2)

Act NewC = CALCULATE(SUM(vw_ShowChargesAndPayForVisits[Charged_Hrs]),WEEKNUM(ClientsList[StartDate] = vw_ShowChargesAndPayForVisits[Weeks]))

Error is The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

[tex628]

Act NewC = CALCULATE(SUM(vw_ShowChargesAndPayForVisits[Charged_Hrs]), vw_ShowChargesAndPayForVisits[Weeks] = WEEKNUM(ClientsList[StartDate],2))

Try this.

[Greg_Deckler]

Tough to decipher what is going on here without sample data and understanding table relationships. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Qotsa]

Sorry @Greg_Deckler,

My mistake. They're in 2 seperate tables.

This is their relationship.

[Anonymous · Accepted solution]

HI @Qotsa,

You can try to add calculated column to 'client list' table if it works for your requirement:

Act NewC =
CALCULATE (
 SUM ( vw_ShowChargesAndPayForVisits[Charged_Hrs] ),
 FILTER (
 ALLSELECTED ( vw_ShowChargesAndPayForVisits ),
 vw_ShowChargesAndPayForVisits[Weeks]
 = WEEKNUM ( EARLIER ( ClientsList[StartDate] ), 2 )
 && vw_ShowChargesAndPayForVisits[ClientRef] = EARLIER ( ClientsList[ClientRef] )
 )
)

Regards,
Xiaoxin Sheng

[Qotsa]

Hi @Greg_Deckler,

All in the same SQL table. Not sure how to provide sample data?