topic Re: Count of Workable Inventory in DAX Commands and Tips

Count of Workable Inventory

sandeep_sharma — Wed, 08 Mar 2023 10:13:00 GMT

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

Re: Count of Workable Inventory

sandeep_sharma — Wed, 08 Mar 2023 10:14:18 GMT

Re: Count of Workable Inventory

Anonymous — Mon, 13 Mar 2023 03:18:34 GMT

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

Re: Count of Workable Inventory

tamerj1 — Mon, 13 Mar 2023 06:28:52 GMT

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

Re: Count of Workable Inventory

sandeep_sharma — Mon, 24 Apr 2023 04:57:29 GMT

Re: Count of Workable Inventory

sandeep_sharma — Mon, 24 Apr 2023 05:19:13 GMT

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 :

Re: Count of Workable Inventory

tamerj1 — Mon, 24 Apr 2023 10:27:18 GMT

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

Re: Count of Workable Inventory

sandeep_sharma — Mon, 24 Apr 2023 16:17:24 GMT

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

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

Re: Count of Workable Inventory

tamerj1 — Mon, 24 Apr 2023 16:27:35 GMT

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