Forum Discussion

sandeep_sharma's avatar
sandeep_sharma
Icon for Helper II rankHelper II
3 years ago
Solved

Count of Workable Inventory

Hi All,   I need to count unbilled workable Ticket no. on the 1st of every month. Below is the data where we have received the date, expiration date, and billed date. A ticket number is workable in...
COUNT
DAX count
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi sandeep_sharma 
    Please refer to attached sample file with the proposed solution

    Count = 
COUNTROWS ( 
    FILTER ( 
        'Table',
        MAX ( 'Table'[Received Date], 'Table'[Expiredate] ) <= MIN ( 'Date'[Date] )
            && COALESCE ( 'Table'[Billed Date], TODAY ( ) ) >= MIN ( 'Date'[Date] )
    )
)
  • tamerj1's avatar
    tamerj1
    3 years ago

    sandeep_sharma 
    Yes

    Count = 
COUNTROWS ( 
    FILTER ( 
        ALL ( 'Table' ),
        MAX ( 'Table'[Received Date], 'Table'[Expiredate] ) <= MIN ( 'Date'[Date] )
            && COALESCE ( 'Table'[Billed Date], TODAY ( ) ) >= MIN ( 'Date'[Date] )
    )
)
sandeep_sharma's avatar
sandeep_sharma
Icon for Helper II rankHelper II
3 years ago

Thanks tamerj1 ....This works! You are awesome!

 

However this All measure does not allow me to filter the data basis my LOB...any workaround?

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

sandeep_sharma 

Count =
COUNTROWS (
FILTER (
CALCULATETABLE ( 'Table', ALL ( 'Date' ) ),
MAX ( 'Table'[Received Date], 'Table'[Expiredate] ) <= MIN ( 'Date'[Date] )
&& COALESCE ( 'Table'[Billed Date], TODAY () ) >= MIN ( 'Date'[Date] )
)
)