Forum Discussion

ClemFandango's avatar
ClemFandango
Advocate 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'

 

TypeActual EndPlanned End
122/10/24 
122/10/24 
225/10/24 
323/10/24 
4 22/10/24

 

If today was the 31st Oct, then I would like to count all of the rows (that were last week of the 31st Oct) as per the conditions below.
The result that I am hoping to achieve would be 5 i.e counting all of the rows above with dates.


I want to do the following calculation for everything that is 'type' = "1", "2", "3"

 

 

Completed Lasttwk = CALCULATE(
COUNTROWS(test),
FILTER(
test,
test[Status] = "Completed"
&& WEEKNUM(test[Actual End])
= WEEKNUM(TODAY())-1
&& YEAR(test[Actual End])=YEAR(TODAY())
)
)

 


However if 'type' = "4" then do the following

 

Completed Lasttwk = CALCULATE(
COUNTROWS(test),
FILTER(
test,
test[Status] = "Completed"
&& WEEKNUM(test[Planned End])
= WEEKNUM(TODAY())-1
&& YEAR(test[Planned End])=YEAR(TODAY())
)
)

 


I am looking for a sum of the COUNTROWS for both of the above calculations.
Any ideas how i can apply this logic to correctly COUNTROWS of the 'Test' table.

 

Any ideas gratefully received

  • 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()))
    )
    )

2 Replies

  • 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
      Advocate II

      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