Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png2.png

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

View solution in original post

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

View solution in original post

8 REPLIES 8

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 :

sandeep_sharma_0-1682313312794.png

 

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

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

 

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

@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
Super User
Super User

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

1.png2.png

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

 

 

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

sandeep_sharma_0-1678270449585.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.