Calculate total count by today's date

[saurabhsahu0111]

Hello Group Members, I am stuck in a situation where I have to findout: 1-Number of open issues as of today's date (Including past dated item if open) 2-Number of open issues after today's date.

Please assist. Thank you.

Ticket             Status      Last Date to close

XXX                  Closed      04-09-2020

ABC                 Open         01-09-2020

DFG                 Open        31-08-2020

GHJ                 Closed      01-09-2020

FHT                 Open        10-09-2020

FGH                 Open       15-09-2020

[amitchandak · Accepted solution]

@saurabhsahu0111 , Try like

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] =<today()) || (table[Status] ="Closed" && [Last Date to close] >today())))

or

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] <today())))

or

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] <= today())))

[v-diye-msft]

Hi @saurabhsahu0111 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Greg_Deckler · Accepted solution]

@saurabhsahu0111 - Seems like:

Measure 1 = COUNTROWS(FILTER('Table',[Status]="Open" && [Last Date to Close] <= TODAY())

Measure 2 = COUNTROWS(FILTER('Table',[Status]="Open" && [Last Date to Close] > TODAY())

I feel like I am missing something. 

You may find this handy - 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[amitchandak · Accepted solution]

@saurabhsahu0111 , Try like

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] =<today()) || (table[Status] ="Closed" && [Last Date to close] >today())))

or

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] <today())))

or

calculate(countrows(Table), filter(Table, (table[Status] ="Open" && [Last Date to close] <= today())))

[saurabhsahu0111]

Thanks @amitchandak for the resolution, it's working.