Count of Workable Inventory

[sandeep_sharma]

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 Date	Expiredate	Billed Date	Should be counted as workable on day 1 of all the following months
431460	05 February 2022	03 February 2022	05 May 2023	Mar, Apr and May
953852	01 January 2022	23 January 2022	05 February 2023	Feb
857550	08 February 2022	26 February 2022	28 February 2022	Not workable in any of the months
580747	04 April 2022	04 April 2022	24 April 2022	Not workable in any of the months
453601	08 September 2022	09 May 2022	24 December 2022	Oct, Nov, Dec
791246	09 March 2022	06 July 2022		All months effective Aug
324288	10 May 2022	07 September 2022		All months effective Oct
530181	05 August 2022	04 May 2022	24 November 2022	Sep, Oct, Nov
264721	03 September 2022	02 October 2022		Nov, Dec

Thanks and Regards,

Sandeep

[tamerj1 · Accepted solution]

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]

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 · Accepted solution]

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

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]

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

[tamerj1 · Accepted solution]

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

[v-shex-msft]

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

[sandeep_sharma]