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

Hi tamerj1 

 

Is there any way I can connect the workable count with main date table (while it still has relationship with disconnected table). I am trying to create a view where I also need to show month wise sum of tt column however if I use Main date in the view, I dont see workable count as this measure is not connected with main date table. Below is what I can see :

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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] )
        )
        )