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 a month if it has expired and was received before the 1st day of the month and has not been billed as of the 1st of that month. So in the below case, all yellow colors will be workable in Jan. 

 

I believe I need to create a sepa

 

Ticket no. Received DateExpiredateBilled DateShould be counted as workable on day 1 of all the following months
43146005 February 202203 February 202205 May 2023Mar, Apr and May
95385201 January 202223 January 202205 February 2023Feb
85755008 February 202226 February 202228 February 2022Not workable in any of the months
58074704 April 202204 April 202224 April 2022Not workable in any of the months
45360108 September 202209 May 202224 December 2022Oct, Nov, Dec
79124609 March 202206 July 2022 All months effective Aug
32428810 May 202207 September 2022 All months effective Oct
53018105 August 202204 May 202224 November 2022Sep, Oct, Nov
26472103 September 202202 October 2022 Nov, Dec

 

Thanks and Regards,

Sandeep

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] )
    )
)

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI sandeep_sharma,

    According to your description, It seem like a common multiple date fields analysis requirement. For this scenario, I'd like to suggest you take a look at the following blog 'start date', 'end date' parts if helps:

    Before You Post, Read This 

    Regards,

    Xiaoxin Sheng

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