Count of Cases based on Billing

[Jorgast]

Hello Power BI Community,

I have been tasked with finding customer calls in relation to when their bill was generated.

What i want to know is the number of cases created within 90days starting from the Invoice Date. Can someone help?

I have 3 tables

Invoice Table / Case Table / Date Table.

The Date table is joined to the Invoice table in the Invoice Created Date

The Date table is joined to the case table in the Created Date

I have tried the following options and haven't gotten anywhere

1. Calculate(Count(Case number), 'Case Table'[Account Number] = 'Invoice Table' [Account Number], DateDiff('Case Table'[Created Date] , 'Invoice Table' [Invoice Created Date], DAY) <= 90

2. CountRows(Filter( 'Case Table', Datediff(DateDiff('Case Table'[Created Date] , 'Invoice Table' [Invoice Created Date], DAY) <= 90)

INVOICE

Account Number	Customer Name	Invoice Created Date	Invoice Due Date	Last Payment	Amount Owed	Generated	Cases submitted Column Results
3000	James Bond	2/1/2021	3/1/2021	1/15/2021	100.00	Auto	0
3001	Dr Evil	2/1/2021	3/1/2021	1/15/2021	100.00	Manual	4
3002	Thanos	2/1/2021	3/1/2021	1/15/2021	100.00	Auto	1
3003	Dr Doom	2/1/2021	3/1/2021	1/15/2021	100.00	Auto	1
3004	Dr Octopus	2/1/2021	3/1/2021	1/15/2021	100.00	Auto	1
3005	Spiderman	2/1/2021	3/1/2021	1/15/2021	100.00	Auto	1
3001	Dr Evil	2/15/2021	3/1/2021	1/15/2021	100.00	Manual	4
3001	Dr Evil	3/1/2021	3/1/2021	2/15/2021	100.00	Manual	2
3001	Dr Evil	3/15/2021	3/1/2021	2/15/2021	100.00	Manual	2

CASE

Case number	Account number	Created Date	Case Type
1	3000	11/20/2020	Sales
2	3001	2/15/2021	Billing
3	3002	2/5/2021	Sales
4	3003	2/6/2021	General
5	3004	2/7/2021	Escalation
6	3005	2/20/2021	Service
7	3001	2/28/2021	General
8	3001	3/20/2021	General
9	3001	3/31/2021	General

[Jorgast]

Here is some sample data with the desired results and table relationships

Case_Number	Account Number	Created_Date	Type
106666294	105	7/2/2020 14:42	General
108685542	105	8/4/2020 13:08	General
113941850	105	10/30/2020 13:52	General
113941903	105	10/30/2020 13:52	General
113941904	105	10/30/2020 13:52	General
113941905	105	10/30/2020 13:52	General
113941935	105	10/30/2020 13:52	General
114825850	105	11/16/2020 14:32	General
114834145	105	11/16/2020 15:39	General
114836041	105	11/16/2020 15:53	General
114836042	105	11/16/2020 15:53	General
115481035	105	11/27/2020 18:02	General
125478900	105	12/5/2020 18:02	Billing
130049318	105	12/17/2020 16:59	General
130092846	105	1/13/2021 8:59	Billing
130121467	105	1/25/2021 11:11	Billing
104442800	912	12/1/2020 12:41	General
107441239	912	12/14/2020 20:32	General
108498152	912	12/31/2020 12:03	General
111348884	912	1/17/2021 13:37	General

Invoice #	Account Number	Invoice Date	Prev Payment	Amount Due	Desired Result - Count of Cases within 90 days of Invoice Date
657931	105	11/20/2020 0:00	-615.06	-615.06	5
660098	105	12/21/2020 0:00	-470.89	144.17	2
2190056	912	12/1/2020 0:00	-2208.13	-2208.13	4
2199971	912	2/1/2021 0:00	0	0	0

[Jorgast · Accepted solution]

I had some help and was able to figure it out.

Column = Calculate(Count(Case[Account Number]), Filter(Case Table,

Invoice [Account Number] = Case[Account Number] &&

Case[Created_Date] >= Invoice[Invoice Date] && Case[Created_Date] <= Invoice[Invoice Date] +90)

[amitchandak]

@Jorgast , I case these tables are joins on [Account Number]  you can create a new measure

countx(summarize('Invoice Table' ,'Invoice Table' [Account Number] , "_1" , if(DateDiff(Min( 'Invoice Table' [Invoice Created Date]),Min('Case Table'[Created Date]), DAY) <= 90,1,blank())),[_1])

Or Create a new  column in invoice table

datediff('Invoice Table' [Invoice Created Date], minx( filter('Case Table','Case Table'[Account Number] = 'Invoice Table' [Account Number]),[Created Date]), DAY)

refer how to copy column values - https://www.youtube.com/watch?v=czNHt7UXIe8

[Jorgast]

In this case, i have the Invoice table and the case table joined to the date table, so I can have a 1:Many relationship

[v-kelly-msft]

Hi @Jorgast ,

Can you provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!