Cumulative count outside of date range

[Anonymous]

Hi everyone,

I have to calculate cumulative total units under warranty for a specific product.

I know how to get the total count number but I can't find a way to demonstrate cumulative by date period.

I'm using a Date table as my slicer.

As you can see below this shows the total of new units under warranty every month.

But this is what I need (orange column):

I need to be able to show the total cumulative up until that month, even if my Date slicer is not in the range of the data.

i.e: Jan-18 shows 149 units under warranty cumulative. But if I change my Date slicer to start on Jan-18, I still want Jan-18 to show the total cumulative from the previous dates (Oct, Nov and Dec-17). Therefore remain at 149 because that's the real total.

This is the code I'm using but not working:

Warranty_Qty_Cumulative_Start = CALCULATE(
COUNT('tblWarranty'[Parent - Serial Number]),
USERELATIONSHIP('tblWarranty'[Contract Start Date],'Date'[Date]),
FILTER(
ALL('tblWarranty'[Contract Start Date]),
'tblWarranty'[Contract Start Date] <= MAX('Date'[Date])
)
)

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[danextian · Accepted solution]

Hi @Anonymous ,

ALL('tblWarranty'[Contract Start Date]) only removes the filter from the Contract Start Date  so if your count by another column, you will see an unexpected result. Please try this:

Warranty_Qty_Cumulative_Start =
CALCULATE (
    COUNT ( 'tblWarranty'[Parent - Serial Number] ),
    USERELATIONSHIP ( 'tblWarranty'[Contract Start Date], 'Date'[Date] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

This 

[Anonymous]

Thank you @danextian ,

Your solution works perfectly.

I appreciate you taking your time to help.

Have an awesome day!

[Anonymous]

If you show your table it would be easier... but... 

Try to substitute "<= MAX('Date'[Date])" for =EARLIER('Date'[Date])

Complete code bellow.

Warranty_Qty_Cumulative_Start = CALCULATE(
COUNT('tblWarranty'[Parent - Serial Number]),
USERELATIONSHIP('tblWarranty'[Contract Start Date],'Date'[Date]),
FILTER(
ALL('tblWarranty'[Contract Start Date]),
'tblWarranty'[Contract Start Date] =EARLIER('Date'[Date])
)
)

[Anonymous]

Hi @Anonymous ,

Thank you for the quick response.

Power BI is not accepting the 'Date'[Date] after the EARLIER.

[Anonymous]

Sorry, try to change 'Date'[Date] for a column, maybe 'tblWarranty'[Contract End Date] 

[Anonymous]

Could you show your table?

[Anonymous]

Here is an example:

Parent Serial Number	Contract Start Date	Contract Completion Date
1	4/12/2017	4/11/2019
2	4/12/2017	4/11/2019
3	4/12/2017	4/11/2019
4	4/12/2017	4/11/2019
5	4/12/2017	4/11/2019
6	4/12/2017	4/11/2019
7	8/28/2018	8/26/2020
8	8/28/2018	8/26/2020
9	8/28/2018	8/26/2020
10	8/28/2018	8/26/2020
11	8/28/2018	8/26/2020
12	8/28/2018	8/26/2020
13	8/28/2018	8/26/2020
14	8/28/2018	8/26/2020
15	8/28/2018	8/26/2020
16	1/25/2017	1/25/2019
17	4/7/2019	9/29/2021
18	4/7/2019	9/8/2021
19	4/7/2019	10/11/2021
20	4/7/2019	4/5/2021
21	4/7/2019	4/5/2021
22	4/7/2019	4/5/2021
23	4/7/2019	4/5/2021
24	4/7/2019	4/5/2021
25	4/7/2019	4/5/2021
26	4/7/2019	4/5/2021
27	4/7/2019	4/5/2021
28	6/12/2017	6/11/2020
29	2/10/2016	2/9/2019
30	4/7/2019	4/5/2022
31	2/5/2019	6/4/2019
32	4/7/2019	8/4/2019
33	10/13/2016	10/12/2019
34	10/13/2016	10/12/2019
35	7/6/2017	7/4/2020
36	3/23/2018	3/21/2021
37	10/12/2016	10/11/2019
38	10/12/2016	10/11/2019
39	10/12/2016	10/11/2019
40	3/28/2017	3/26/2020
41	3/23/2018	3/21/2021
42	3/23/2018	3/21/2021
43	3/23/2018	3/21/2021
44	2/28/2016	2/26/2019
45	4/29/2016	4/28/2019
46	4/29/2016	4/28/2019
47	4/29/2016	4/28/2019
48	4/29/2016	4/28/2019
49	4/29/2016	4/28/2019
50	4/29/2016	4/28/2019
51	4/29/2016	4/28/2019
52	4/29/2016	4/28/2019
53	2/11/2016	2/9/2019
54	2/11/2016	2/9/2019
55	2/11/2016	2/9/2019
56	3/9/2017	3/7/2020
57	11/16/2016	11/15/2019
58	5/16/2017	5/14/2020
59	5/16/2017	5/14/2020
60	9/14/2016	9/13/2019
61	9/19/2016	9/18/2019
62	3/2/2018	2/29/2020
63	3/2/2018	2/29/2020
64	3/2/2018	2/29/2020
65	1/28/2016	1/26/2019
66	2/28/2018	2/26/2021
67	2/28/2018	2/26/2021
68	2/28/2018	2/26/2021
69	2/28/2018	2/26/2021
70	2/28/2018	2/26/2021
71	2/28/2018	2/26/2021
72	2/28/2018	2/26/2021
73	9/26/2016	9/25/2019
74	9/26/2016	9/25/2019
75	9/26/2016	9/25/2019
76	9/26/2016	9/25/2019
77	10/24/2017	10/22/2020
78	1/14/2018	1/13/2019
79	4/12/2017	4/10/2020
80	7/16/2019	10/23/2019

[amitchandak]

I think you need active contracts. Please refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin