Forum Discussion

youconnect's avatar
youconnect
Helper I
6 years ago
Solved

Countrows last date

I have this table id state date 1 active 01/01/2020 1 reserved 05/01/2020 1 suspended 08/01/2020 1 sold 15/01/2020 2 active 02/01/2020 2 suspended 03/01/2020 2 ...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi youconnect ,

     

    I am understanding your logic and have made the following calculation:

    count_active_id = 
var last_active_date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[id],Sheet2[date]),Sheet2[state]="active"))
var last_date = CALCULATE(MAX(Sheet2[date]),ALLEXCEPT(Sheet2,Sheet2[id]))
return CALCULATE(COUNTROWS(FILTER(Sheet2,last_active_date=last_date)))

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

youconnect's avatar
youconnect
Helper I
6 years ago

edhans 

No.
last date for id 1 is 15/01/2020 and state is not active, not count

last date for id 2 is 05/01/2020 and state is active, count

last date for id 3 is 05/01/2020 and state is active, count

last date for id 4 is 08/01/2020 and state is not active, not count.

So count is 2

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi youconnect ,

 

I am understanding your logic and have made the following calculation:

count_active_id = 
var last_active_date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[id],Sheet2[date]),Sheet2[state]="active"))
var last_date = CALCULATE(MAX(Sheet2[date]),ALLEXCEPT(Sheet2,Sheet2[id]))
return CALCULATE(COUNTROWS(FILTER(Sheet2,last_active_date=last_date)))

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • youconnect's avatar
    youconnect
    Helper I
    6 years ago

    Thanks V-lianl-msft it worked like a charm.

    Another question: If I want only the records before 01-01-2020 how do that?