Forum Discussion

ClemFandango's avatar
ClemFandango
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Logic query for IF, AND, || with CALCULATE and COUNTROWS function

Hi all,   I am sure this is very simple, but I am really struggling with this bit of logic for CALCULATE, COUNTROWS & FILTER Functions. I have the following table called 'Test'   Type Actual ...
"Show and tell"
COUNTROWS
DAX
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    ClemFandango , Try using 

     

    Completed Lasttwk =
    CALCULATE(
    COUNTROWS('Test'),
    FILTER(
    'Test',
    ('Test'[Type] IN {1, 2, 3} && 'Test'[Status] = "Completed" && WEEKNUM('Test'[Actual End]) = WEEKNUM(TODAY()) - 1 && YEAR('Test'[Actual End]) = YEAR(TODAY()))
    ||
    ('Test'[Type] = 4 && 'Test'[Status] = "Completed" && WEEKNUM('Test'[Planned End]) = WEEKNUM(TODAY()) - 1 && YEAR('Test'[Planned End]) = YEAR(TODAY()))
    )
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

ClemFandango , Try using 

 

Completed Lasttwk =
CALCULATE(
COUNTROWS('Test'),
FILTER(
'Test',
('Test'[Type] IN {1, 2, 3} && 'Test'[Status] = "Completed" && WEEKNUM('Test'[Actual End]) = WEEKNUM(TODAY()) - 1 && YEAR('Test'[Actual End]) = YEAR(TODAY()))
||
('Test'[Type] = 4 && 'Test'[Status] = "Completed" && WEEKNUM('Test'[Planned End]) = WEEKNUM(TODAY()) - 1 && YEAR('Test'[Planned End]) = YEAR(TODAY()))
)
)

ClemFandango's avatar
ClemFandango
Icon for Advocate II rankAdvocate II
1 year ago

bhanu_gautam Quickest response ever!

Thanks so much - i think that has done it. I will give it closer look tomorrow and mark it as accepted. 

Huge thanks again