cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Poweredrodrigo
Frequent Visitor

Cumulative count outside of date range

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.

underwarranty_month.PNG

 

But this is what I need (orange column):

cumulative_warranty.PNG

 

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

 

1 ACCEPTED SOLUTION
danextian
Community Champion
Community Champion

Hi @Poweredrodrigo ,

 

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 

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

9 REPLIES 9
Icey
Community Support
Community Support

Hi @Poweredrodrigo ,

 

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
Community Champion
Community Champion

Hi @Poweredrodrigo ,

 

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 

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Thank you @danextian ,

 

Your solution works perfectly.

I appreciate you taking your time to help.

 

Have an awesome day!

Williamspsouza
Helper III
Helper III

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

 

Hi @Williamspsouza ,

Thank you for the quick response.

 

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

error.PNG

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

Could you show your table?

Here is an example:

 

Parent Serial NumberContract Start DateContract Completion Date
14/12/20174/11/2019
24/12/20174/11/2019
34/12/20174/11/2019
44/12/20174/11/2019
54/12/20174/11/2019
64/12/20174/11/2019
78/28/20188/26/2020
88/28/20188/26/2020
98/28/20188/26/2020
108/28/20188/26/2020
118/28/20188/26/2020
128/28/20188/26/2020
138/28/20188/26/2020
148/28/20188/26/2020
158/28/20188/26/2020
161/25/20171/25/2019
174/7/20199/29/2021
184/7/20199/8/2021
194/7/201910/11/2021
204/7/20194/5/2021
214/7/20194/5/2021
224/7/20194/5/2021
234/7/20194/5/2021
244/7/20194/5/2021
254/7/20194/5/2021
264/7/20194/5/2021
274/7/20194/5/2021
286/12/20176/11/2020
292/10/20162/9/2019
304/7/20194/5/2022
312/5/20196/4/2019
324/7/20198/4/2019
3310/13/201610/12/2019
3410/13/201610/12/2019
357/6/20177/4/2020
363/23/20183/21/2021
3710/12/201610/11/2019
3810/12/201610/11/2019
3910/12/201610/11/2019
403/28/20173/26/2020
413/23/20183/21/2021
423/23/20183/21/2021
433/23/20183/21/2021
442/28/20162/26/2019
454/29/20164/28/2019
464/29/20164/28/2019
474/29/20164/28/2019
484/29/20164/28/2019
494/29/20164/28/2019
504/29/20164/28/2019
514/29/20164/28/2019
524/29/20164/28/2019
532/11/20162/9/2019
542/11/20162/9/2019
552/11/20162/9/2019
563/9/20173/7/2020
5711/16/201611/15/2019
585/16/20175/14/2020
595/16/20175/14/2020
609/14/20169/13/2019
619/19/20169/18/2019
623/2/20182/29/2020
633/2/20182/29/2020
643/2/20182/29/2020
651/28/20161/26/2019
662/28/20182/26/2021
672/28/20182/26/2021
682/28/20182/26/2021
692/28/20182/26/2021
702/28/20182/26/2021
712/28/20182/26/2021
722/28/20182/26/2021
739/26/20169/25/2019
749/26/20169/25/2019
759/26/20169/25/2019
769/26/20169/25/2019
7710/24/201710/22/2020
781/14/20181/13/2019
794/12/20174/10/2020
807/16/201910/23/2019

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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.